用bulk 實現超快遷移審計表AUD$資料過程一個

之前給客戶寫了個清理審計表的方案，發現從aud$取數時採用了bulk，速度快了很多，分享下程式碼：                                                                                                                      

   CREATE OR REPLACE PROCEDURE "SYS"."ARCHIVE_AUDIT"
as
        v_begintime date;
        v_curtime date;
        v_size BINARY_FLOAT;
BEGIN
        select sysdate into v_begintime from dual;
declare     
CURSOR cur IS
SELECT/*+parallel(a,8) */ * FROM sys.aud$ a WHERE NTIMESTAMP# < v_begintime;
TYPE rec IS TABLE OF sys.aud$%ROWTYPE; 
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
  FETCH cur BULK COLLECT
  INTO recs LIMIT 10000;
  FORALL i IN 1 .. recs.COUNT
 INSERT INTO ORA_MON.DBA_AUDIT_TRAIL_HIST VALUES  recs (i);
  COMMIT;
  select sysdate into v_curtime from dual;
  EXIT WHEN cur%NOTFOUND or (v_curtime > v_begintime+2/24);
  END LOOP;
            execute immediate 'truncate table SYS.AUD$';
    CLOSE cur;
    end;
   SELECT round(BYTES/1024/1024,0) INTO v_size FROM DBA_SEGMENTS WHERE SEGMENT_NAME='AUD$' AND OWNER='SYS';
        if v_size>=50 then
          begin
            execute immediate 'truncate table SYS.AUD$';
          exception
            when others then
               null;
          end;
        end if;
END;