flashback系列文章二（flashback drop）

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

recyclebin被置为on之后，在数据库中drop表，实际上是改名后被放到了回收站。

SQL> create table bin_test(id number);

Table created.

SQL> insert into bin_test values(1);

1 row created.

SQL> insert into bin_test values(2);

1 row created.

SQL> insert into bin_test values(3);

1 row created.

SQL> drop table bin_test;

Table dropped.
SQL> show recyclebin;
SQL> show user
USER is "SYS"
居然没有任何输出，是因为sys用户的drop操作是不会放到回收站的（oracle不推荐sys用户做业务）。

SQL> conn scott/tiger
Connected.

SQL> create table bin_test(id number);

Table created.

SQL> insert into bin_test values(1);

1 row created.

SQL> insert into bin_test values(2);

1 row created.

SQL> insert into bin_test values(3);

1 row created.

SQL> drop table bin_test;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BIN_TEST         BIN$7swqwFDywVjgQKjAZGJVhg==$0 TABLE        2013-12-31:10:20:22

SQL> flashback table bin_test to before drop;

Flashback complete.

SQL> select * from bin_test;

        ID
----------
         1
         2
         3
SQL> show recyclebin
回收站的bin_test又被重命名回去了。
也可以使用rename给表恢复的表改个名字

SQL> flashback table bin_test to before drop rename to bin_test1;

Flashback complete.

SQL> select * from bin_test1;

        ID
----------
         1
         2
         3
再试试drop多个同名的表，恢复的应该是哪个？

SQL> create table bin_test1(id number);

Table created.

SQL> insert into bin_test1 values(1);

1 row created.

SQL> drop table bin_test1;

Table dropped.

SQL> create table bin_test1(id number);

Table created.

SQL> insert into bin_test1 values(2);

1 row created.

SQL> drop table bin_test1;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BIN_TEST1        BIN$7swqwFD1wVjgQKjAZGJVhg==$0 TABLE        2013-12-31:10:30:02
BIN_TEST1        BIN$7swqwFD0wVjgQKjAZGJVhg==$0 TABLE        2013-12-31:10:29:34
可以看到回收站里面有两个表了

SQL> flashback table bin_test1 to before drop;

Flashback complete.

SQL> select * from bin_test1;

        ID
----------
         2
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BIN_TEST1        BIN$7swqwFD0wVjgQKjAZGJVhg==$0 TABLE        2013-12-31:10:29:34
flashback drop恢复了最新删除的表。

实际上，回收站里面的对象是可以当表来进行查询的

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
BIN_TEST1        BIN$7swqwFD0wVjgQKjAZGJVhg==$0 TABLE        2013-12-31:10:29:34
SQL> select * from "BIN$7swqwFD0wVjgQKjAZGJVhg==$0";

        ID
----------
         1

1、回收站的相关视图：recyclebin/user_recyclebin/dba_recyclebin. 也可以通过show recyclebin来进行查看

2、可以手动清除回收站内容：purge recyclebin(或purge table original_name);

3、彻底删除表：drop table table_name purge;
4、也可以通过purge tablespace tablespace_name或者purge tablespace tablespace_name user username来清理回收站的对象
5、也可以在session级别关闭recyclebin，alter session set recyclebin on/off;
6、flashback drop恢复表时，会把索引和triger等对象一并恢复，但是名称不会变回原来，需要手动改下。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26613085/viewspace-1066013/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26613085/viewspace-1066013/