Oracle10g 回收站及徹底刪除table : drop table xx purge 以及drop flash
drop後的表被放在回收站(user_recyclebin)裡,而不是直接刪除掉。這樣,回收站裡的表資訊就可以被恢復,或徹底清除。
1.透過查詢回收站user_recyclebin獲取被刪除的表資訊,然後使用語句
[@more@]flashback table
將回收站裡的表恢復為原名稱或指定新名稱,表中資料不會丟失。
若要徹底刪除表,則使用語句:drop table
2.清除回收站裡的資訊
清除指定表:purge table;
清除當前使用者的回收站:purge recyclebin;
清除所有使用者的回收站:purge dba_recyclebin;
===============================================================================
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as test
清除指定表: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 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 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
------------------------------ -------------------------------- --------- -------------------------
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 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;
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
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ------- -------------------------------- ---------- --------- ------- ----------- ------------ -----
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;
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-999000/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle10g 回收站及徹底刪除table : drop table xx purge 以及flashbackOracle
- DROP TABLE ** CASCADE CONSTRAINTS PURGEAI
- oracle 誤刪表 drop tableOracle
- oracle drop table purge無備份bbed恢復(1/3)Oracle
- oracle drop table purge無備份bbed恢復(2/3)Oracle
- oracle drop table purge無備份bbed恢復(3/3)Oracle
- 執行drop table base purge出現ora_00604
- drop table和truncate table的區別
- MySQL DROP TABLE刪除表報錯'ERROR 1051 (42S02): Unknown table'MySqlError
- flashback drop/query/table/database/archiveDatabaseHive
- How To Efficiently Drop A Table With Many Extents
- Drop table時候會徹底刪除index, Truncate 時候會清除index 但是index資料還是保留在HIndex
- 閃回之 回收站、Flashback Drop (table、index、trigger等)Index
- drop apply INSTANTIATION for one tableAPP
- audit drop table為什麼不行
- Drop table cascade constraintsAI
- [doc]How To Efficiently Drop A Table With Many Extents
- Truncate table 詳解及與delete,drop 的區別delete
- Truncate table詳解及與delete,drop的區別delete
- Backup And Recovery User's Guide-使用閃回刪除來回退DROP TABLE操作GUIIDE
- 深入解析 oracle drop table內部原理Oracle
- oracle10g刪除Table的困惑Oracle
- Oracle回收站及flashback drop(上)Oracle
- Oracle回收站及flashback drop(下)Oracle
- Flashback Drop閃回刪除功能實踐(基於回收站)
- MySQL資料災難挽救之drop tableMySql
- Drop Table Fails With ORA-600 [15264]AI
- Oracle Drop表(purge)恢復(ODU)Oracle
- Oracle Purge和drop的區別Oracle
- oracle 11g之alter table drop unused columns checkpoint刪除表不可用列系列二Oracle
- MySQL 5.6 drop database時,table metadata lock等待MySqlDatabase
- 資料庫審計(create/alter/drop table、user、tablespace)資料庫
- 教你怎麼從Windows10徹底刪除FlashWindows
- v$lock之alter table drop column與alter table set unused column區別系列五
- 徹底刪除ORACLEOracle
- 外來鍵約束drop table cascade constraintsAI
- 主子表drop table constraints cascade的測試AI
- Drop Table Fails With ORA-600 [15264] [ID 338953.1]AI