表空间不足时Oracle管理Recyclebin的方法

发布时间:2016-10-28 00:00:00 编辑:嘉辉 手机版

  我们知道,在Oracle 10g中,drop table如果没有加 purge选项.实际数据并没有删除,因而没有释放表空间.

  那么当被删除的对象占用了所有空间时,会发生什么? 相信看完以下测试,一切皆会明白

  YXYUP@dbatest>selecttablespace_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name=’TBS’ group by tablespace_name;

  TABLESPACE_NAME SUM(BYTES)/1024/1024

  TBS 17

  Elapsed: 00:00:00.02

  YXYUP@dbatest>selectsegment_name,bytes/1024/1024,tablespace_name from user_segments;

  SEGMENT_NAME BYTES/1024/1024 TABLESPACE_NAME

  TEST_BAK 6 TBS

  TEST01 6 TBS

  Elapsed: 00:00:00.07

  YXYUP@dbatest>createtable test02 tablespace tbs as select * from dba_objects;

  create table test02 tablespace tbs as select * from dba_objects

  *

  ERROR at line 1:

  ORA-01652: unable to extend temp segment by 128 in tablespace TBS

  Elapsed: 00:00:00.73

  YXYUP@dbatest>droptable test01;

  Table dropped.

  Elapsed: 00:00:00.04

  YXYUP@dbatest>showrecyclebin;

  ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

  TEST01 BIN$VPH4ibnKVd/gQAB/AQA0Nw==$0 TABLE 2008-08-21:14:56:55

  YXYUP@dbatest>

  YXYUP@dbatest>select* from tab;

  TNAME TABTYPE CLUSTERID

  BIN$VPH4ibnKVd/gQAB/AQA0Nw==$0 TABLE

  TEST_BAK TABLE

  Elapsed: 00:00:00.01

  YXYUP@dbatest>createtable test02 tablespace tbs as select * from dba_objects;

  Table created.

  Elapsed: 00:00:00.84

  YXYUP@dbatest>showrecyclebin;

  YXYUP@dbatest>select* from tab;

  TNAME TABTYPE CLUSTERID

  TEST_BAK TABLE

  TEST02 TABLE

  Elapsed: 00:00:00.01

  上面测试可以看出,当表空间空闲空间不足时,Oracle就会将recyclebin中的表给彻底删除,然后腾出空间来放要创建的新对象.

  那么,如果recyclebin中有多个被drop的对象时,Oracle会怎么清理呢,是全部清除,还是怎样? 通过下面的测试我们行到答案.

  YXYUP@dbatest>showrecyclebin;

  YXYUP@dbatest>select* from tab;

  TNAME TABTYPE CLUSTERID

  TEST_BAK TABLE

  TEST02 TABLE

  Elapsed: 00:00:00.01

  YXYUP@dbatest>droptable test02;

  Table dropped.

  Elapsed: 00:00:00.04

  YXYUP@dbatest>droptable test_bak;

  Table dropped.

  Elapsed: 00:00:00.02

  YXYUP@dbatest>select* from tab;

  TNAME TABTYPE CLUSTERID

  BIN$VPH4ibnLVd/gQAB/AQA0Nw==$0 TABLE

  BIN$VPH4ibnMVd/gQAB/AQA0Nw==$0 TABLE

  Elapsed: 00:00:00.01

  YXYUP@dbatest>showrecyclebin;

  ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

  TEST02 BIN$VPH4ibnLVd/gQAB/AQA0Nw==$0 TABLE 2008-08-21:15:14:43

  TEST_BAK BIN$VPH4ibnMVd/gQAB/AQA0Nw==$0 TABLE 2008-08-21:15:14:51

  YXYUP@dbatest>createtable test01 tablespace tbs as select * from dba_objects;

  Table created.

  Elapsed: 00:00:00.79

  YXYUP@dbatest>select* from tab;

  TNAME TABTYPE CLUSTERID

  BIN$VPH4ibnMVd/gQAB/AQA0Nw==$0 TABLE

  TEST01 TABLE

  Elapsed: 00:00:00.01

  YXYUP@dbatest>flashbacktable TEST02 to before drop ;

  flashback table TEST02 to before drop

  *

  ERROR at line 1:

  ORA-38305: object not in RECYCLE BIN

  Elapsed: 00:00:00.00

  YXYUP@dbatest>flashbacktable TEST_BAK to before drop ;

  Flashback complete.

  Elapsed: 00:00:00.02

  YXYUP@dbatest>select* from tab;

  TNAME TABTYPE CLUSTERID

  TEST_BAK TABLE

  TEST01 TABLE

  Elapsed: 00:00:00.01

  通过上面测试可以看出.

  当recyclebin中有多个被删除的同时,并在表空间不足以放下要创建的新对象时,Oracle会FIFO的方式从回收站中自动彻底删除.

本文已影响877
+1
0