flashback drop的例子

Oralce中的flashback drop是基于recyclebin的
show parameter recycle;
alter system set recyclebin=on;
alter system set recyclebin=off;
alter session set recyclebin=on;
alter session set recyclebin=off;

sqlplus test/test
select * from A;
drop table A;
show recyclebin;
select * from recyclebin;
flashback table A to before drop;
select * from A;

purge tablespace xx;
purge tablespace xx user xx;
purge recyclebin;
purge dba_recyclebin;
drop table xx purge;
purge index xx;

如果drop之后又创建了A,则再flashback时会出错, 需要rename
drop table A;
create table A (name varchar(3));
flashback table A to before drop rename to B;
select * from B;

如果连续drop同一个表, flashback时会恢复最近的一次drop.
SQL> select * from B;        ID----------         1         2SQL> drop table B;  Table dropped.SQL> create table B (id number);Table created.SQL> insert into B values(1);1 row created.SQL> commit;Commit complete.SQL> drop table B;Table dropped.SQL> show recyclebin;ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME---------------- ------------------------------ ------------ -------------------B                BIN$wxLSKF68PT/gRAALXeAIIA==$0 TABLE        2012-06-22:09:13:16B                BIN$wxLSKF67PT/gRAALXeAIIA==$0 TABLE        2012-06-22:09:12:31SQL> flashback table B to before drop;Flashback complete.SQL> select * from B;        ID----------         1SQL> show recyclebin;ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME---------------- ------------------------------ ------------ -------------------B                BIN$wxLSKF67PT/gRAALXeAIIA==$0 TABLE        2012-06-22:09:12:31

 flashback drop的限制
只能用于非系统表空间和本地管理表空间
对象的参考约束不会被恢复
能否恢复成功取决于空间是否被重用覆盖
删除表时,视图也会被删除,但view不会存在在recyclebin中
recyclebin中的对象只能select 不能做其它操作