關(guān)于表空間不足時(shí)Oracle管理Recyclebin

　　我們知道,在Oracle 10g中,drop table如果沒有加 purge選項(xiàng).實(shí)際數(shù)據(jù)并沒有刪除,因而沒有釋放表空間.那么當(dāng)被刪除的對(duì)象占用了所有空間時(shí)，會(huì)發(fā)生什么? 出門一起來看看!

　　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

　　上面測(cè)試可以看出,當(dāng)表空間空閑空間不足時(shí),Oracle就會(huì)將recyclebin中的表給徹底刪除,然后騰出空間來放要?jiǎng)?chuàng)建的新對(duì)象.

　　那么,如果recyclebin中有多個(gè)被drop的對(duì)象時(shí),Oracle會(huì)怎么清理呢,是全部清除,還是怎樣? 通過下面的測(cè)試我們行到答案.

　　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

　　通過上面測(cè)試可以看出.

　　當(dāng)recyclebin中有多個(gè)被刪除的同時(shí),并在表空間不足以放下要?jiǎng)?chuàng)建的新對(duì)象時(shí),Oracle會(huì)FIFO的方式從回收站中自動(dòng)徹底刪除.

【表空間不足時(shí)Oracle管理Recyclebin】相關(guān)文章：

Oracle數(shù)據(jù)庫(kù)臨時(shí)表管理技巧06-15

Oracle認(rèn)證：Oracle避免全表掃描方式10-04

Oracle認(rèn)證：空間管理三個(gè)技巧05-13

Oracle避免全表掃描方式10-22

xp系統(tǒng)注冊(cè)表空間不足的解決方法是什么07-06

Oracle重做日志文件管理技巧08-31

Oracle認(rèn)證：ORACLE綁定變量BINDPEEKING08-25

Oracle認(rèn)證：Oracle控制件文件修復(fù)07-27

Oracle認(rèn)證：Oracle內(nèi)存結(jié)構(gòu)研究-PGA篇09-22

電腦磁盤空間不足怎么辦09-08