下边我们采用job的形式来删除回收站对象：
10:51:28 SYS@oralhr> SELECT D.owner,COUNT(1) FROM dba_recyclebin D GROUP BY D.owner;
OWNER COUNT(1)
------------------------------------------------------------ ----------
SYS 215333
10:53:25 SYS@oralhr> CREATE TABLE XB_recyclebin_LHR NOLOGGING AS
10:53:26 2 SELECT ROWNUM RN, 'PURGE ' || A.type || ' ' || A.owner || '."' || A.object_name || '"' EXEC_SQL
10:53:26 3 FROM dba_recyclebin A
10:53:26 4 where a.type = 'TABLE';
Table created.
10:53:41 SYS@oralhr> CREATE INDEX IDX_recyclebin_rn on XB_recyclebin_LHR(rn) NOLOGGING ;
Index created.
10:53:55 SYS@oralhr> create table XB_SPLIT_JOB_LHR
10:54:05 2 (
10:54:05 3 startrownum NUMBER(18),
10:54:05 4 endrownum NUMBER(18),
10:54:05 5 flag NUMBER(1)
10:54:05 6 );
Table created.
10:54:06 SYS@oralhr> SELECT * FROM xb_split_job_lhr;
no rows selected
10:54:12 SYS@oralhr> CREATE OR REPLACE PROCEDURE pro_split_job_lhr AUTHID CURRENT_USER IS
10:54:51 2 ---------------------------------------------------------------------
10:54:51 3 -- copy on 2012/4/2 23:28:21 by lhr
10:54:51 4 --function：该存过用来分隔数据来建立job
10:54:51 5 --需要进行处理的数据量 ,需要处理的表加rn列，值取rownum，rn列加索引
10:54:51 6
10:54:51 7 --alter table tmp_dp_idp_lhr add rn number;
10:54:51 8 /* CREATE INDEX IDX_tmp_dp_idp_lhr_rn on tmp_dp_idp_lhr(rn)
10:54:51 9 TABLESPACE SDH_INDEX ONLINE NOLOGGING COMPUTE STATISTICS PARALLEL;*/
10:54:51 10
10:54:51 11 /* create table XB_SPLIT_JOB_LHR
10:54:51 12 (
10:54:51 13 startrownum NUMBER(18),
10:54:51 14 endrownum NUMBER(18),
10:54:51 15 flag NUMBER(1)
10:54:51 16 )*/
10:54:51 17 --------------------------------------------------------------------
10:54:51 18
10:54:51 19 n NUMBER; --创建的job数
10:54:51 20 j NUMBER := 0;
10:54:51 21 n_startrownum NUMBER;
10:54:51 22 n_endrownum NUMBER;
10:54:51 23 n_patchnum NUMBER := 40000; -- 每批处理的记录数 ----modify
10:54:51 24 v_jobname VARCHAR2(200);
10:54:51 25 v_count NUMBER; --需要处理的表的数据量
10:54:51 26
10:54:51 27 BEGIN
10:54:51 28
10:54:51 29 SELECT COUNT(1) INTO v_count FROM XB_recyclebin_LHR; ----modify
10:54:51 30
10:54:51 31 --需要创建的job个数
10:54:51 32 n := trunc(v_count / n_patchnum) + 1;
10:54:51 33
10:54:51 34 EXECUTE IMMEDIATE 'truncate table xb_split_job_lhr';
10:54:51 35 WHILE j < n LOOP
10:54:51 36
10:54:51 37 --得到rownum
10:54:51 38 n_startrownum := j * n_patchnum + 1;
10:54:51 39
10:54:51 40 IF j = n - 1 THEN
10:54:51 41
10:54:51 42 n_endrownum := v_count;
10:54:51 43 ELSE
10:54:51 44 n_endrownum := (j + 1) * n_patchnum;
10:54:51 45 END IF;
10:54:51 46
10:54:51 47 INSERT INTO xb_split_job_lhr
10:54:51 48 (startrownum, endrownum)
10:54:51 49 VALUES
10:54:51 50 (n_startrownum, n_endrownum);
10:54:51 51 COMMIT;
10:54:51 52
10:54:51 53 j := j + 1;
10:54:51 54 END LOOP;
10:54:51 55
10:54:51 56 --循环创建job
10:54:51 57 j := 0;
10:54:51 58
10:54:51 59 FOR cur IN (SELECT * FROM xb_split_job_lhr) LOOP
10:54:51 60
10:54:52 61 v_jobname := 'JOB_SUBJOB_SPLIT_LHR' || (j + 1);
10:54:52 62 dbms_scheduler.create_job(job_name => v_jobname,
10:54:52 63 job_type => 'STORED_PROCEDURE',
10:54:52 64 job_action => 'PRO_SUB_SPLIT_LHR', --modify
10:54:52 65 number_of_arguments => 2,
10:54:52 66 start_date => SYSDATE + 1 / 5760, -- 15秒后启动作业
10:54:52 67 repeat_interval => NULL,
10:54:52 68 end_date => NULL,
10:54:52 69 job_class => 'DEFAULT_JOB_CLASS',
10:54:52 70 enabled => FALSE,
10:54:52 71 auto_drop => TRUE,
10:54:52 72 comments => 'to split job_subjob_Split_lhr');
10:54:52 73 COMMIT;
10:54:52 74
10:54:52 75 dbms_scheduler.set_job_argument_value(job_name => v_jobname,
10:54:52 76 argument_position => 1,
10:54:52 77 argument_value => cur.startrownum);
10:54:52 78 COMMIT;
10:54:52 79 dbms_scheduler.set_job_argument_value(job_name => v_jobname,
10:54:52 80 argument_position => 2,
10:54:52 81 argument_value => cur.endrownum);
10:54:52 82 COMMIT;
10:54:52 83 dbms_scheduler.enable(v_jobname);
10:54:52 84 j := j + 1;
10:54:52 85 END LOOP;
10:54:52 86 COMMIT;
10:54:52 87
10:54:52 88 -----等待所有的子job执行完
10:54:52 89
10:54:52 90 LOOP
10:54:52 91
10:54:52 92 SELECT COUNT(1)
10:54:52 93 INTO v_count
10:54:52 94 FROM xb_split_job_lhr t
10:54:52 95 WHERE t.flag IS NULL;
10:54:52 96
10:54:52 97 IF v_count = 0 THEN
10:54:52 98 EXIT;
10:54:52 99 ELSE
10:54:52 100 dbms_lock.sleep(10); ---存过休息10秒
10:54:52 101 END IF;
10:54:52 102
10:54:52 103 END LOOP;
10:54:52 104 EXECUTE IMMEDIATE 'purge dba_recyclebin';
10:54:52 105 EXCEPTION
10:54:52 106 WHEN OTHERS THEN
10:54:52 107 NULL;
10:54:52 108
10:54:52 109 END pro_split_job_lhr;
10:54:54 110 /
Procedure created.
10:55:17 SYS@oralhr> show error
No errors.
10:55:21 SYS@oralhr> create or replace procedure pro_sub_split_lhr(p_startrownum number,
10:55:24 2 p_endrownum number) is
10:55:24 3
10:55:24 4 begin
10:55:24 5
10:55:24 6 for cur in (SELECT A.EXEC_SQL
10:55:24 7 FROM XB_recyclebin_LHR A ---modify
10:55:24 8 where A.rn <= p_endrownum
10:55:24 9 and A.rn >= p_startrownum) loop
10:55:24 10 begin
10:55:24 11 EXECUTE IMMEDIATE CUR.EXEC_SQL;
10:55:24 12 exception
10:55:24 13 when others then
10:55:24 14 null;
10:55:24 15 end;
10:55:24 16 end loop;
10:55:24 17
10:55:24 18 commit;
10:55:24 19
10:55:24 20 --更新标志
10:55:24 21 update xb_split_job_lhr t
10:55:24 22 set t.flag = 1
10:55:24 23 where t.startrownum = p_startrownum
10:55:24 24 and t.endrownum = p_endrownum;
10:55:24 25 commit;
10:55:24 26
10:55:24 27 exception
10:55:24 28
10:55:24 29 when others then
10:55:24 30
10:55:24 31 null;
10:55:24 32
10:55:24 33 end pro_sub_split_lhr;
10:55:25 34 /
Procedure created.
10:55:26 SYS@oralhr> show error
No errors.
10:55:29 SYS@oralhr> exec pro_split_job_lhr;
单独开窗口重新计算清空回收站的速度：
SYS@oralhr> set time on
11:04:38 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;
COUNT(1)
----------
211055
11:06:00 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;
COUNT(1)
----------
189105
11:08:00 SYS@oralhr> select (211055-189105)/80 from dual;
(234164-227432)/150
-------------------
274.375 ====》说明每秒大约删掉275条记录
11:08:10 SYS@oralhr> select 189105/275/60 from dual;
189105/275/60
-------------
11.4609091 ====》说明删除18W数据大约需要11分钟
11:09:10 SYS@oralhr>
