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
???????? 0
执行闪回删除表的命令并查询数据是否完整：
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
???????? 0
2.闪回删除并将表重命名
BYS@bys1>select * from test5;
??????? ID NAME
---------- ----------
???????? 2 b???????
BYS@bys1>drop table test5;
Table dropped.
BYS@bys1>flashback table test5 to before drop rename to test6;
Flashback complete.
BYS@bys1>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
