回收站及flashback drop实验部分(3)

原创
02/08 20:03
阅读数 0

下边我们采用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>


 


 About Me

..........................................................................................................................................................................................................

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

本文在ITpub(http://blog.itpub.net/26736162)和博客园(http://www.cnblogs.com/lhrbest)有同步更新

本文地址:http://blog.itpub.net/26736162/viewspace-2121136/http://blog.itpub.net/26736162/viewspace-2121137/

本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b) 

小麦苗分享的其它资料:http://blog.itpub.net/26736162/viewspace-1624453/

联系我请加QQ好友(642808185),注明添加缘由

于 2016-06-24 10:00~ 2016-06-27 19:00 在中行完成

【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

..........................................................................................................................................................................................................


本文分享自微信公众号 - DB宝(lhrdba)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部