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

原创
01/28 09:16
阅读数 0


 

等待十几分钟后查看数据:

SYS@oralhr> select * from xb_split_job_lhr;

 

STARTROWNUM  ENDROWNUM       FLAG

----------- ---------- ----------

          1      40000

      40001      80000

      80001     120000

     120001     159915

 

SYS@oralhr>

SYS@oralhr> col owner for a5

SYS@oralhr> col CPU_USED for a18

SYS@oralhr> col ELAPSED_TIME for a18

SYS@oralhr> select OWNER,JOB_NAME,CPU_USED,ELAPSED_TIME,RUNNING_INSTANCE from dba_scheduler_running_jobs;

 

OWNER JOB_NAME                       CPU_USED           ELAPSED_TIME       RUNNING_INSTANCE

----- ------------------------------ ------------------ ------------------ ----------------

SYS   JOB_SUBJOB_SPLIT_LHR1          +000 00:10:18.36   +000 00:19:15.29                  1

SYS   JOB_SUBJOB_SPLIT_LHR2          +000 00:10:14.71   +000 00:19:15.07                  1

SYS   JOB_SUBJOB_SPLIT_LHR3          +000 00:10:12.77   +000 00:19:14.95                  1

SYS   JOB_SUBJOB_SPLIT_LHR4          +000 00:10:14.70   +000 00:19:14.78                  1

 

SYS@oralhr>

 

若系统CPU强劲的话,该SQL会很快完成的,查询dba_scheduler_running_jobs视图将无数据表示job已完成。



4  实验总结

1、11.2.0.4中若回收站对象过多的情况下,dba_free_space查询过慢的问题已经解决了

2、实验二的脚本具有通用性,很多操作可以同时执行的时候我们可以修改该程序




第二章 实验中用到的SQL总结

 

实验一:

dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \

-gdbname oralhr  -sid oralhr \

-sysPassword oracle -systemPassword lhr \

-datafileDestination 'DATA2/' -recoveryAreaDestination 'DATA2/' \

-redoLogFileSize 50 \

-storageType ASM -asmsnmpPassword lhr  -diskGroupName 'DATA2' \

-characterset AL32UTF8 -nationalCharacterSet AL16UTF16 \

-sampleSchema false \

-automaticMemoryManagement true -totalMemory 2048 \

-databaseType OLTP  \

-emConfiguration NONE

 

show parameter recy

create table tb_20160627_lhr as select * from dual;

drop table tb_20160627_lhr;

select * from dba_recyclebin;

drop table tb_20160627_lhr;

drop table tb_20160627_lhr;

select * from dba_recyclebin;

 

 

实验二:

begin

 

  for cur in 1 .. 100000 loop

  

    execute immediate 'create table tb_recyclebin_' || cur ||

                      ' nologging tablespace users as select * from dual';

    execute immediate 'create index idx_recyclebin_' || cur ||

                      ' on tb_recyclebin_' || cur ||' (dummy) nologging tablespace users';

  

  end loop;

end;

/

 

begin

 

  for cur in 1 .. 100000 loop

  

    execute immediate 'create table tb_recyclebin_lhr_' || cur ||

                      ' nologging tablespace users as select * from dual';

    execute immediate 'create index idx_recyclebin_lhr_' || cur ||

                      ' on tb_recyclebin_lhr_' || cur ||' (dummy) nologging tablespace users';

  

  end loop;

 

end;

/

 

begin

 

  for cur in 1 .. 100000 loop

  

    execute immediate 'create table tb_recyclebin_lhr1_' || cur ||

                      ' nologging tablespace users as select * from dual';

    execute immediate 'create index idx_recyclebin_lhr1_' || cur ||

                      ' on tb_recyclebin_lhr1_' || cur ||' (dummy) nologging tablespace users';

  

  end loop;

 

end;

 

begin

  for cur in (SELECT d.table_name

                FROM dba_tables d

               WHERE d.table_name like 'TB_RECYCLEBIN%') loop

    execute immediate 'drop table ' || cur.table_name;

  end loop;

end;

/

 

 

CREATE TABLE XB_recyclebin_LHR NOLOGGING AS

SELECT ROWNUM RN, 'PURGE ' || A.type || ' ' || A.owner || '."' || A.object_name || '"' EXEC_SQL

  FROM dba_recyclebin A

 where a.type = 'TABLE';

 

 CREATE INDEX IDX_recyclebin_rn  on XB_recyclebin_LHR(rn) NOLOGGING ;

 

 create table XB_SPLIT_JOB_LHR

 (

   startrownum NUMBER(18),

   endrownum   NUMBER(18),

   flag        NUMBER(1)

 );

 

 col CPU_USED for a18

 col ELAPSED_TIME for a18

 select OWNER,JOB_NAME,CPU_USED,ELAPSED_TIME,RUNNING_INSTANCE from dba_scheduler_running_jobs;


 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
分享
返回顶部
顶部