Oracle回收站及flashback drop(下)-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle回收站及flashback drop(下)

简介: Oracle回收站及flashback drop(下) 本文接着上篇文章继续讲解:http://blog.itpub.net/26736162/viewspace-2121136/ 第二章 实验部分 二.

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

 

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后不会进入回收站空间。

 

 

 

二.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

 

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已完成。

 

 

二.4  实验总结

111.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-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 在中行完成

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

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

 

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章