1.誤删除表，使用flashback drop功能找回
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
SQL> select * from test;
A
----------
1
2
3
4
5
6
7
8
0.
SQL> show parameter recyclebin; -驗證資源回收筒是否打開--如未打開不能使用閃回DROP--11G預設打開
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
SQL> show recyclebin;
SQL> drop table test;
Table dropped.
查詢資源回收筒，删除的表在裡面，并被系統自己指令--BIN$開頭一長串---從ORIGINAL NAME 列可以看到表的原名是test
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$387tHPzSqO/gQKjA3QEnzg==$0 TABLE 2013-06-23:17:29:01
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$387tHPzSqO/gQKjA3QEnzg==$0 TABLE
使用資源回收筒中的BIN$的表名也能查詢到表中資料
SQL> select * from "BIN$387tHPzSqO/gQKjA3QEnzg==$0";
A
----------
1
2
3
4
5
6
7
8
執行閃回删除表的指令并查詢資料是否完整：
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from "BIN$387tHPzSqO/gQKjA3QEnzg==$0";
select * from "BIN$387tHPzSqO/gQKjA3QEnzg==$0"
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
SQL> select * from test;
A
----------
1
2
3
4
5
6
7
8
2.閃回删除并将表重命名
[email protected]>select * from test5;
ID NAME
---------- ----------
2 b
[email protected]>drop table test5;
Table dropped.
[email protected]>flashback table test5 to before drop rename to test6;
Flashback complete.
[email protected]>select * from test6;
ID NAME
---------- ----------
2 b
3.資源回收筒内檢視删除的表，單個删除資源回收筒裡的表、全部清空資源回收筒。
SQL> create table test2 as select * fromtest1;
表已建立。
SQL> insert into test2values('00002','baishan2');
已建立 1 行。
SQL> select * from test2;
ID NAME
----- ----------
00001 baishan
00002 baishan2
SQL> show recyclebin
SQL> drop table test1;
表已删除。
删除表後檢視資源回收筒裡，可以看到删除的表名，及删除時間
SQL> showrecyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
TEST1 BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0TABLE 2013-02-10:12:17:11
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0 TABLE
TEST TABLE
TEST2 TABLE
SQL> create table test3 as select * fromtest2;
表已建立。
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0 TABLE
TEST TABLE
TEST2 TABLE
TEST3 TABLE
DROP時使用purge參數删除的表被直接删除，不存放在資源回收筒。
SQL> drop table test2 purge;
表已删除。
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
TEST1 BIN$kUfBJ3qcTSiLxqU6hmoEcw==$0TABLE 2013-02-10:12:17:11
清空資源回收筒
SQL> purge recyclebin;
資源回收筒已清空。
SQL> drop table test2;
表已删除。
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
TEST2 BIN$UabVC8wiRx+GdsN4VT5bRw==$0TABLE 2013-02-12:17:10:46
SQL> drop table test3;
表已删除。
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
TEST2 BIN$UabVC8wiRx+GdsN4VT5bRw==$0TABLE 2013-02-12:17:10:46
TEST3 BIN$n8CZgrwoSRO16TFB/dXOcA==$0TABLE 2013-02-12:17:11:35
清除資源回收筒中的test2表。
SQL> purge tabletest2;
表已清除。
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------------------------------------- ------------ -------------------
TEST3 BIN$n8CZgrwoSRO16TFB/dXOcA==$0TABLE 2013-02-12:17:11:35
SQL> flashback table test3 to before drop;
閃回完成。
SQL> show recyclebin
SQL> select count(*) from test3;
COUNT(*)
----------
2