1、开启recyclebin：alter system set recyclebin=on/alter session set recyclebin=on (10gR2以上)
2、flashback drop table的步骤：
(1) 查看dba_recyclebin，确认具体需要恢复的表
select owner,object_name,original_name,partition_name,type,ts_name,createtime,droptime,canundrop from dba_recyclebin;
(2) 恢复表(可以重命名):
如果只是需要表中的数据，则可以直接从recyclebin中取得。
如果表只drop过一次，可以用原对象名来恢复：flashback table original_tabname to before drop rename to new_tabname;
如果表drop过多次，要用recyclebin中的对象名来恢复:flashback table current_tabname to before drop rename to new_tabname.
(如果new_tabname已经存在，则flashback会报错，但不会影响原来new_tabname表)
flashback无法恢复全文索引(from yangtingkun).
http://yangtingkun.itpub.net/post/468/228085
SQL> create user user1 identified by abc default tablespace testtbs temporary tablespace temp quota unlimited on testtbs;
User created.
SQL>
SQL> grant connect,resource to user1;
Grant succeeded.
SQL> conn user1/abc
Connected.
SQL> create table tab1 (id number);
Table created.
SQL> insert into tab1 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table tab1;
Table dropped.
SQL> create table tab1(name varchar2(10),seq number);
Table created.
SQL> insert into tab1 values ('test1',1);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table tab1;
Table dropped.
下面恢复表
先查询recyclebin
select object_name,original_name,partition_name,type,ts_name,createtime,droptime,can_undrop from user_recyclebin;
SQL> select object_name,original_name,partition_name,type,ts_name,createtime,droptime,can_undrop from user_recyclebin;
OBJECT_NAME ORIGINAL_N PARTITION_ TYPE TS_NAME CREATETIME DROPTIME CAN_UN
------------------------------ ---------- ---------- ---------- ---------- -------------------- -------------------- ------
BIN$QcLhBBNUt9HgQAB/AQAeTw==$0 TAB1 TABLE TESTTBS 2007-12-21:10:45:51 2007-12-21:10:46:01 YES
BIN$QcLhBBNVt9HgQAB/AQAeTw==$0 TAB1 TABLE TESTTBS 2007-12-21:10:46:38 2007-12-21:10:47:01 YES
查询先建的表：
SQL> select * from "BIN$QcLhBBNUt9HgQAB/AQAeTw==$0";
ID
----------
1
查询后建的表：
SQL> select * from "BIN$QcLhBBNVt9HgQAB/AQAeTw==$0";
NAME SEQ
-------------------- ----------
test1 1
恢复先建的表为new_tabl：
SQL> flashback table "BIN$QcLhBBNUt9HgQAB/AQAeTw==$0" to before drop rename to new_tab1;
Flashback complete.
SQL> select * from new_tab1;
ID
----------
1
恢复后建的表为new_tab2:
SQL> flashback table "BIN$QcLhBBNVt9HgQAB/AQAeTw==$0" to before drop rename to new_tab2;
Flashback complete.
SQL> select * from new_tab2;
NAME SEQ
-------------------- ----------
test1 1
下面看看rename to的表已经存在的情况：
SQL> drop table new_tab1;
Table dropped.
SQL> select object_name,original_name,partition_name,type,ts_name,createtime,droptime,can_undrop from user_recyclebin;
OBJECT_NAME ORIGINAL_N PARTITION_ TYPE TS_NAME CREATETIME DROPTIME CAN_UN
------------------------------ ---------- ---------- ---------- ---------- -------------------- -------------------- ------
BIN$QcLhBBNWt9HgQAB/AQAeTw==$0 NEW_TAB1 TABLE TESTTBS 2007-12-21:10:45:51 2007-12-21:11:03:14 YES
SQL> create table tab1 (tdate date);
Table created.
SQL> insert into tab1 values (sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> flashback table "BIN$QcLhBBNWt9HgQAB/AQAeTw==$0" to before drop rename to tab1;
flashback table "BIN$QcLhBBNWt9HgQAB/AQAeTw==$0" to before drop rename to tab1
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
flashback出错,因为tab1表已经存在.
SQL> select * from tab1;
TDATE
------------
21-DEC-07
执行flashback并没有影响到已经存在的表。
SQL>
SQL> select * from "BIN$QcLhBBNWt9HgQAB/AQAeTw==$0";
ID
----------
1
