本文接着上篇文章继续讲解:http://blog.itpub.net/26736162/viewspace-2121136/
项目 | primary db |
db 类型 | 单实例 |
db version | 11.2.0.2.0 |
db 存储 | ASM |
本次我们模拟2个实验:
1、系统表空间的对象不能闪回
2、在版本为11.2.0.3及以下的情况下,当回收站对象过多时查询表空间大小时涉及到dba_free_space很慢,用purge dba_recyclebin又太慢,所以采用job来批量删除
首先建立测试库并打开回收站功能:
[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
Copyright (c) 1982, 2010, Oracle. All rights reserved.
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后不会进入回收站空间。
我们遵循如下的实验步骤:
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
Copyright (c) 1982, 2010, Oracle. All rights reserved.
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>
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已完成。
1、11.2.0.4中若回收站对象过多的情况下,dba_free_space查询过慢的问题已经解决了
2、实验二的脚本具有通用性,很多操作可以同时执行的时候我们可以修改该程序
实验一:
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-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 在中行完成
【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】
..........................................................................................................................................................................................................