[原創] 當表空間不足時,Oracle是這樣管理Recyclebin

我們知道,在Oracle 10g中,drop table如果沒有加 purge選項.實際資料並沒有刪除,因而沒有釋放表空間.
那麼當被刪除的物件佔用了所有空間時，會發生什麼? 相信看完以下測試,一切皆會明白

YXYUP@dbatest>select tablespace_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>select segment_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>create table 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>drop table test01;

Table dropped.

Elapsed: 00:00:00.04

YXYUP@dbatest>show recyclebin;
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>create table test02 tablespace tbs as select * from dba_objects;

Table created.

Elapsed: 00:00:00.84

YXYUP@dbatest>show recyclebin;
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>show recyclebin;
YXYUP@dbatest>select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST_BAK                       TABLE
TEST02                         TABLE

Elapsed: 00:00:00.01
YXYUP@dbatest>drop table test02;

Table dropped.

Elapsed: 00:00:00.04
YXYUP@dbatest>drop table 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>show recyclebin;
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>create table 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>flashback table 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>flashback table 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的方式從回收站中自動徹底刪除.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7364032/viewspace-432974/，如需轉載，請註明出處，否則將追究法律責任。