- 相關(guān)推薦
關(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發(fā)展歷程08-25
Oracle認(rèn)證體系介紹01-30
oracle認(rèn)證考試詳情06-09
Oracle DBA職業(yè)要求08-31
Oracle認(rèn)證考試介紹05-03
Oracle認(rèn)證種類介紹07-21
關(guān)于Oracle認(rèn)證考試時(shí)間09-27