运维日记|我该如何高效清理Oracle大表历史数据

#$% truncate #$% delete nologging @#$#% delete parallel $#$%^$ delete by rowid %$%^#drop database.¥&%
复制

declare
  cursor cur_rowid is
    select dbms_rowid.rowid_create(1,
                                   b.data_object_id,
                                   a.relative_fno,
                                   a.block_id,
                                   0) begin_rowid,
           dbms_rowid.rowid_create(1,
                                   b.data_object_id,
                                   a.relative_fno,
                                   a.block_id + blocks - 1,
                                   999) end_rowid
      from dba_extents a, dba_objects b
     where a.segment_name = b.object_name
       and a.owner = b.owner
       and b.object_name = 'OB3'
       and b.owner = 'JOE'
     order by a.relative_fno, a.block_id;
  r_sql varchar2(4000);
begin
  FOR cur in cur_rowid LOOP
    r_sql := 'delete OB3 where object_type =' || '''' || 'SYNONYM' || '''' ||
             ' and rowid between :1 and :2';
    EXECUTE IMMEDIATE r_sql
      using cur.begin_rowid, cur.end_rowid;
    COMMIT;
  END LOOP;
end;
/
复制

SET SERVEROUTPUT ON
BEGIN
    DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task');
EXCEPTION WHEN OTHERS THEN
NULL;
END;
/


DECLARE
    l_task     VARCHAR2(30) :=   'test_task';
    l_sql_stmt VARCHAR2(32767);
    l_try      NUMBER;
    l_status   NUMBER;
BEGIN
    -- Create the TASK
    DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task);
    -- Chunk the table by the ROWID
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID
  (
      TASK_NAME   => l_task,
      TABLE_OWNER => 'JOE',    <<<用户名
      TABLE_NAME  => 'OB2',    <<<表名
      BY_ROW      => TRUE,    <<<表示chunk_size为行数，否则表示块数
      CHUNK_SIZE  => 2500     <<<自定义chunk size，这里表示2500行为一个chunk
    );
    -- DML to be execute in parallel
  l_sql_stmt := 'delete OB2 where object_type   = ''SYNONYM'' and rowid BETWEEN :start_id AND   :end_id';    <<<想要执行的SQL语句
    -- Run the task
    DBMS_PARALLEL_EXECUTE.RUN_TASK
  (
      TASK_NAME      => l_task,
      SQL_STMT       => l_sql_stmt,
      LANGUAGE_FLAG  =>   DBMS_SQL.NATIVE,
      PARALLEL_LEVEL => 2    <<<自定义执行并行度
    );
    -- If there is error, RESUME it for at most 2 times.
    l_try := 0;
    l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
    WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
    LOOP
      l_try := l_try + 1;
      DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task);
      l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task);
    END LOOP;
    -- Done with processing; drop the task
    DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task);
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM);
END;
/
复制

DBA_PARALLEL_EXECUTE_TASKS
DBA_PARALLEL_EXECUTE_CHUNKS
dba_scheduler_jobs
复制