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

原创
08/07 17:08
阅读数 0

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>

 


 

 


 

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