Oracle从“回收站”恢复删除的表

2.查看回收站里的内容

SQL> show recyclebin;

or
SQL> select * from user_recyclebin;

下面将创建的表删除后放入回收站

SQL> show user;
USER is "HXL"
SQL> create table test_rbin(val number);

Table created.

SQL> insert into test_rbin(val) values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

TEST_RBIN BIN$S4u3UNw0QQ6t6LRxMMz7hQ==$0 TABLE 2012-06-22:15:01:30

3.从回收站恢复删除的表

可以使用如下语句从回收站恢复删除的表

sql > flashback table <> to before drop;

例如我们恢复刚才删除的表

SQL> flashback table test_rbin to before drop;

Flashback complete.

SQL> select * from test_rbin;

VAL
----------
10

在恢复的过程的同时我们可以将表另外命名,命令如下:

sql>flashback table << dropped table name >> to before drop rename to <>;
sql>flashback table test_rbin to before drop rename to test_rbin1;

Oracle从回收站恢复是按照"降序"恢复的,比如连续3次删除同样一个表(删除后再创建,再删除),恢复的是先恢复最后一次删除的表.

SQL> show user;
USER is "HXL"
SQL> drop table test_rbin;

Table dropped.

SQL> create table test_rbin (col1 number);

Table created.

SQL> insert into test_rbin values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> create table test_rbin (col1 number);

Table created.

SQL> insert into test_rbin values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> create table test_rbin (col1 number);

Table created.

SQL> insert into test_rbin values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table test_rbin;

Table dropped.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

TEST_RBIN BIN$gGHkV/xQSSaQ5bG0PikSAg==$0 TABLE 2012-06-22:15:31:21
TEST_RBIN BIN$xVKygKwJTJa+8zmu4BJzvQ==$0 TABLE 2012-06-22:15:30:53
TEST_RBIN BIN$R7As9PsYRva7CY6cnAjROw==$0 TABLE 2012-06-22:15:30:23
TEST_RBIN BIN$0R+cRKhDTFu+8ShBjLDpqg==$0 TABLE 2012-06-22:15:29:27

SQL> select * from test_rbin1;

COL1
----------
3

SQL> select * from test_rbin2;

COL1
----------
2

SQL> select * from test_rbin3;

COL1
----------
1