Oracle回收站及flashback drop(下)

本文接著上篇文章繼續講解：http://blog.itpub.net/26736162/viewspace-2121136/

第二章實驗部分

二.1 實驗環境介紹

專案	primary db
db 型別	單例項
db version	11.2.0.2.0
db 儲存	ASM

二.2 實驗目標

本次我們模擬2個實驗：

1、系統表空間的物件不能閃回

2、在版本為11.2.0.3及以下的情況下，當回收站物件過多時查詢表空間大小時涉及到dba_free_space很慢，用purge dba_recyclebin又太慢，所以採用job來批次刪除

二.3 實驗過程

二.3.1 實驗一：

首先建立測試庫並開啟回收站功能：

[ZT1MXP11:oracle]:/oracle>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

Copying database files

1% complete

3% complete

10% complete

17% complete

24% complete

31% complete

35% complete

Creating and starting Oracle instance

37% complete

42% complete

47% complete

52% complete

53% complete

56% complete

58% complete

Registering database with Oracle Restart

64% complete

Completing Database Creation

68% complete

71% complete

75% complete

85% complete

96% complete

100% complete

Look at the log file "/oracle/app/oracle/cfgtoollogs/dbca/oralhr/oralhr.log" for further details.

[ZT1MXP11:oracle]:/oracle>more /oracle/app/oracle/cfgtoollogs/dbca/oralhr/oralhr.log

Copying database files

DBCA_PROGRESS : 1%

DBCA_PROGRESS : 3%

DBCA_PROGRESS : 10%

DBCA_PROGRESS : 17%

DBCA_PROGRESS : 24%

DBCA_PROGRESS : 31%

DBCA_PROGRESS : 35%

Creating and starting Oracle instance

DBCA_PROGRESS : 37%

DBCA_PROGRESS : 42%

DBCA_PROGRESS : 47%

DBCA_PROGRESS : 52%

DBCA_PROGRESS : 53%

DBCA_PROGRESS : 56%

DBCA_PROGRESS : 58%

Registering database with Oracle Restart

DBCA_PROGRESS : 64%

Completing Database Creation

DBCA_PROGRESS : 68%

DBCA_PROGRESS : 71%

DBCA_PROGRESS : 75%

DBCA_PROGRESS : 85%

DBCA_PROGRESS : 96%

DBCA_PROGRESS : 100%

Database creation complete. For details check the logfiles at:

/oracle/app/oracle/cfgtoollogs/dbca/oralhr.

Database Information:

Global Database Name:oralhr

System Identifier(SID):oralhr

[ZT1MXP11:oracle]:/oracle>

[ZT1MXP11:oracle]:/oracle>ORACLE_SID=oralhr

[ZT1MXP11:oracle]:/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 27 10:12:18 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SYS@oralhr> show parameter recy

NAME TYPE VALUE

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

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string OFF

SYS@oralhr> alter system set recyclebin=on scope=spfile;

System altered.

SYS@oralhr> startup force; ====》慎用，不推薦

ORACLE instance started.

Total System Global Area 3089920000 bytes

Fixed Size 2250360 bytes

Variable Size 721422728 bytes

Database Buffers 2348810240 bytes

Redo Buffers 17436672 bytes

Database mounted.

Database opened.

SYS@oralhr> show parameter recy

NAME TYPE VALUE

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

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string ON

SYS@oralhr> create table tb_20160627_lhr as select * from dual;

Table created.

SYS@oralhr> drop table tb_20160627_lhr;

Table dropped.

SYS@oralhr> select * from dba_recyclebin;

no rows selected

SYS@oralhr> create table tb_20160627_lhr tablespace users as select * from dual;

Table created.

SYS@oralhr> drop table tb_20160627_lhr;

Table dropped.

SYS@oralhr> select * from dba_recyclebin;

OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME

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

DROPTIME DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE

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

SYS BIN$Njoq6PZtAGzgUxa8wKsAbA==$0 TB_20160627_LHR DROP TABLE USERS 2016-06-27:11:16:01

2016-06-27:11:16:05 7268816 YES YES 450051 450051 450051 8

SYS@oralhr>

說明SYSTEM表空間的表drop後不會進入回收站空間。

二.3.2 實驗二：

我們遵循如下的實驗步驟：

1、建立10W張表，並建立索引

2、開啟回收站

3、刪除建立的表

4、查詢dba_free_space檢視

5、清空回收站後再查詢dba_free_space檢視

實驗開始：我們首先利用建表的指令碼建立出10W張表，可以多開幾個視窗，並行建表加快速度，另外，10W張表大約佔用users表空間6G多，這個需要注意一下：

等待10W張表建好的時候取消建表語句：

[ZT1MXP11:oracle]:/oracle>ORACLE_SID=oralhr

[ZT1MXP11:oracle]:/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 27 09:12:18 2016

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SYS@oralhr> begin

2

3 for cur in 1 .. 100000 loop

4

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

6 ' nologging tablespace users as select * from dual';

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

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

9

10 end loop;

11 end;

12 /

PL/SQL procedure successfully completed.

SYS@oralhr>

SYS@oralhr> SELECT count(1) FROM dba_tables d WHERE d.table_name like 'TB_RECYCLEBIN%';

COUNT(1)

----------

187796

SYS@oralhr> SELECT sum(d.bytes)/1024/1024 FROM dba_segments d WHERE d.segment_name like '%TB_RECYCLEBIN%';

SUM(D.BYTES)/1024/1024

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

11737.25

開啟回收站：

SYS@oralhr> show parameter recy

NAME TYPE VALUE

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

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string OFF

SYS@oralhr> alter system set recyclebin=on scope=spfile;

System altered.

SYS@oralhr> startup force; ====》慎用，不推薦

ORACLE instance started.

Total System Global Area 3089920000 bytes

Fixed Size 2250360 bytes

Variable Size 721422728 bytes

Database Buffers 2348810240 bytes

Redo Buffers 17436672 bytes

Database mounted.

Database opened.

SYS@oralhr> show parameter recy

NAME TYPE VALUE

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

buffer_pool_recycle string

db_recycle_cache_size big integer 0

recyclebin string ON

接下來我們drop掉剛剛建立的表：

SYS@oralhr> WITH wt1 AS

2 (SELECT ts.TABLESPACE_NAME,

3 df.all_bytes,

4 decode(df.TYPE,

5 'D',

6 nvl(fs.FREESIZ, 0),

7 'T',

8 df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,

9 df.MAXSIZ,

10 ts.BLOCK_SIZE,

11 ts.LOGGING,

12 ts.FORCE_LOGGING,

13 ts.CONTENTS,

14 ts.EXTENT_MANAGEMENT,

15 ts.SEGMENT_SPACE_MANAGEMENT,

16 ts.RETENTION,

17 ts.DEF_TAB_COMPRESSION,

18 df.ts_df_count

19 FROM dba_tablespaces ts,

20 (SELECT 'D' TYPE,

21 TABLESPACE_NAME,

22 COUNT(*) ts_df_count,

23 SUM(BYTES) all_bytes,

24 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ

25 FROM dba_data_files d

26 GROUP BY TABLESPACE_NAME

27 UNION ALL

28 SELECT 'T',

29 TABLESPACE_NAME,

30 COUNT(*) ts_df_count,

31 SUM(BYTES) all_bytes,

32 SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))

33 FROM dba_temp_files d

34 GROUP BY TABLESPACE_NAME) df,

35 (SELECT TABLESPACE_NAME,

36 SUM(BYTES) FREESIZ

37 FROM dba_free_space

38 GROUP BY TABLESPACE_NAME

39 UNION ALL

40 SELECT tablespace_name,

41 SUM(d.BLOCK_SIZE * a.BLOCKS) bytes

42 FROM gv$sort_usage a,

43 dba_tablespaces d

44 WHERE a.tablespace = d.tablespace_name

45 GROUP BY tablespace_name) fs

46 WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME

47 AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

48 SELECT (SELECT A.TS#

49 FROM V$TABLESPACE A

50 WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,

51 t.TABLESPACE_NAME TS_Name,

52 round(t.all_bytes / 1024 / 1024) ts_size_M,

53 round(t.freesiz / 1024 / 1024) Free_Size_M,

54 round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

55 round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,

56 round(MAXSIZ / 1024 / 1024/1024, 3) MAX_Size_g,

57 round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /

58 MAXSIZ,

59 3) USED_per_MAX,

60 round(t.BLOCK_SIZE) BLOCK_SIZE,

61 t.LOGGING,

62 t.ts_df_count

63 FROM wt1 t

64 UNION ALL

65 SELECT to_number('') TS#,

66 'ALL TS:' TS_Name,

67 round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,

68 round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,

69 round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,

70 round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,

71 round(SUM(MAXSIZ) / 1024 / 1024/1024) MAX_Size,

72 to_number('') "USED,% of MAX Size",

73 to_number('') BLOCK_SIZE,

74 '' LOGGING,

75 to_number('') ts_df_count

76 FROM wt1 t

77 order by TS#

78 ;

TS# TS_NAME TS_SIZE_M FREE_SIZE_M USED_SIZE_M USED_PER MAX_SIZE_G USED_PER_MAX BLOCK_SIZE LOGGING TS_DF_COUNT

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

0 SYSTEM 1110 10 1100 99.116 32 3.358 8192 LOGGING 1

1 SYSAUX 510 27 483 94.743 32 1.475 8192 LOGGING 1

2 UNDOTBS1 760 222 538 70.765 32 1.641 8192 LOGGING 1

3 TEMP 29 25 4 13.793 32 .012 8192 NOLOGGING 1

4 USERS 24688 2032 22655 91.768 32 69.138 8192 LOGGING 1

ALL TS: 27096.5 2316 24780 91.453 160

6 rows selected.

SYS@oralhr>

SYS@oralhr> SELECT count(1) FROM dba_free_space;

COUNT(1)

----------

254

SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

COUNT(1)

----------

0

SYS@oralhr>

SYS@oralhr> begin

2 for cur in (SELECT d.table_name

3 FROM dba_tables d

4 WHERE d.table_name like 'TB_RECYCLEBIN%') loop

5

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

7

8 end loop;

9 end;

10 /

PL/SQL procedure successfully completed.

SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

COUNT(1)

----------

239829

SYS@oralhr> select count(1) from dba_free_space;

select count(1) from dba_free_space

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SYS@oralhr>

回收站裡有239829條資料，我們查詢dba_free_space檢視很久都不能出結果，接下來只能清空回收站了。

利用purge dba_recyclebin命令清理回收站：

10:39:50 SYS@oralhr> purge dba_recyclebin;

單獨開視窗計算：

SYS@oralhr> set time on

10:43:44 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

COUNT(1)

----------

234164

10:44:11 SYS@oralhr> SELECT count(1) FROM dba_recyclebin;

COUNT(1)

----------

227432

10:48:02 SYS@oralhr> select (234164-227432)/150 from dual;

(234164-227432)/150

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

44.88 ====》說明每秒大約刪掉45條記錄

10:48:05 SYS@oralhr> select 227432/45/60 from dual;

227432/45/60

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

84.2340741 ====》說明刪除22W資料大約需要1個半小時，太慢了

10:48:28 SYS@oralhr>

下邊我們採用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>

等待十幾分鍾後檢視資料：

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;

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-2121137/

本文pdf版：（提取碼：ed9b）

小麥苗分享的其它資料：http://blog.itpub.net/26736162/viewspace-1624453/

聯絡我請加QQ好友(642808185)，註明新增緣由

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

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

Oracle回收站及flashback drop(下)

Oracle回收站及flashback drop(下)

第二章 實驗部分

二.1 實驗環境介紹

二.2 實驗目標

二.3 實驗過程

二.3.1 實驗一：

二.3.2 實驗二：

二.4 實驗總結

第三章 實驗中用到的SQL總結

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

相關文章

第二章實驗部分

第三章實驗中用到的SQL總結