Oracle10g 回收站及徹底刪除table : drop table xx purge 以及flashback

tolywang發表於2010-08-04
drop後的表被放在回收站(user_recyclebin)裡,而不是直接刪除掉。這樣,回收站裡的表資訊就可以被恢復,或徹底清除。
 
1.透過查詢回收站user_recyclebin獲取被刪除的表資訊,然後使用語句


flashback table to before drop [rename to ];
將回收站裡的表恢復為原名稱或指定新名稱,表中資料不會丟失。
若要徹底刪除表,則使用語句:drop table purge;
2.清除回收站裡的資訊
清除指定表:purge table ;
清除當前使用者的回收站:purge recyclebin;
清除所有使用者的回收站:purge dba_recyclebin;
===============================================================================
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as test
SQL> select * from test1;
A B C
-- -- ----------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> create table test2 as select * from test1;s
Table created
SQL> select * from test2;
A B C
-- -- ----------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> drop table test2;
Table dropped
SQL> select object_name, original_name, operation, type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$g5jFmA/OShC6+wsWKJiv2w==$0 TEST1 DROP TABLE
BIN$vQwemDg4R9mK9fYJNdYzvg==$0 TEST2 DROP TABLE
SQL> flashback table test2 to before drop rename to test3;--【to test3】將表重新命名
Done
SQL> select * from test3;
A B C
-- -- ----------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> select * from test2;
select * from test2
ORA-00942: 表或檢視不存在
--徹底刪除表
SQL> drop table test3 purge;
Table dropped
SQL> select * from user_recyclebin where original_name = 'TEST3';
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ------- -------------------------------- ---------- --------- ------- ----------- ------------ -----
SQL> select * from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ------- -------------------------------- ---------- --------- ------- ----------- ------------ -----
BIN$g5jFmA/OShC6+wsWKJiv2w==$0 TEST1 DROP TABLE TP_TEST1 2007-08-23:07:57:28 2007-08-23:07:58:51 1411156 YES YES 53086 53086 53086 896
--清除回收站裡的表資訊test1
SQL> purge table test1;
Done
SQL> select * From user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

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

相關文章