关于表空间不足时Oracle管理Recyclebin
来源:才华咖 本文已影响2.96W人
来源:才华咖 本文已影响2.96W人
我们知道,在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的方式从回收站中自动彻底删除.
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
Perseverance will make you successful英语作文
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
如何理解Javascript的caller,callee,call,apply区别
Modern Technology Makes Life More Conv英语六级作文
如何理解Javascript的caller,callee,call,apply区别
The place of Science and Technology in Modern Life英语作文
如何理解Javascript的caller,callee,call,apply区别
励志:时间管理的时间法则
A Bitter Experience in My Childhood初中英语作文
关于SEFC Book I Unit 7 Cultural Relics的教学方案
英语阅读:VIT declines to acquire Ang Mo Kio property
英语阅读:Life is inevitable consequence of physics
Should Private Cars Be Encouraged in China高中英语作文
双语阅读:Clear Your Mental Space
如何理解JavaScript的caller,callee,call,apply
关于表空间不足时Oracle管理Recyclebin
convenience,vocabulary可数吗?
Choice of large enterprises or small英语四级作文
Should Celebrity Have Privacy英语作文及翻译
关于时间发言稿
英语作文Why Are Bicycles So Commonly Used in China
Low-carbon Lifestyle高中英语作文
UN Chief Urges Leaders to Deliver on Climate Change美文欣赏
The relationship between theory and practice大学英语作文
《You Cant Judge a William by Its Cover》英语美文欣赏
关于时间管理测试题
雅思口语范文:My Experience as an English Learner
The Sprint in College Entrance Exam英语作文
英语阅读:African Union Leaders Talk Mali, Chinese Investmen
英语阅读:Are You Tricked by These Optical Illusions
Relieve Psychological Pressure的英语短文
「雅思写作」the Growth Cycle of A Volcanic Island