Flashback drop是从Oracle 10g开始才有的功能,原理是每个表空间都会有严格回收站的逻辑区域,当drop时,被删除的表及其关联对象不会被物理删除,只是转移到回收站中,给用户提供一个恢复的可能。
使用Flashback drop需要注意以下几点:
1. 对system表空间无效
2. sqlplus的版本不能低于10g,否则很多命令无法使用
下面做个测试:
SQL> create table test as select * from dba_objects;
SQL> drop table test;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$S5L+aNpzQmOScn8VfpJBAA==$0 TABLE 2011-12-15:22:38:30
SQL> create table test as select * from dba_objects where 2=1;
SQL> drop table test;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$XHwrz1OaQaSeq/NQIE85hw==$0 TABLE 2011-12-15:22:39:34
TEST BIN$S5L+aNpzQmOScn8VfpJBAA==$0 TABLE 2011-12-15:22:38:30
此时recyclebin里有两个test表,查看中两个表的数据个数:
SQL> select count(*) from "BIN$XHwrz1OaQaSeq/NQIE85hw==$0";
COUNT(*)
----------
0
SQL> select count(*) from "BIN$S5L+aNpzQmOScn8VfpJBAA==$0";
COUNT(*)
----------
1000000
假设想要恢复后一张表:
SQL> flashback table "BIN$S5L+aNpzQmOScn8VfpJBAA==$0" to before drop;
Flashback complete.
SQL> select count(*) from test;
COUNT(*)
----------
1000000
假设现在还要恢复前一张表,并重新命名为test2:
SQL> flashback table "BIN$XHwrz1OaQaSeq/NQIE85hw==$0" to before drop rename to t
est2;
Flashback complete.
SQL> select count(*) from test2;
COUNT(*)
----------
0
使用Flashback drop需要注意以下几点:
1. 对system表空间无效
2. sqlplus的版本不能低于10g,否则很多命令无法使用
下面做个测试:
SQL> create table test as select * from dba_objects;
SQL> drop table test;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$S5L+aNpzQmOScn8VfpJBAA==$0 TABLE 2011-12-15:22:38:30
SQL> create table test as select * from dba_objects where 2=1;
SQL> drop table test;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$XHwrz1OaQaSeq/NQIE85hw==$0 TABLE 2011-12-15:22:39:34
TEST BIN$S5L+aNpzQmOScn8VfpJBAA==$0 TABLE 2011-12-15:22:38:30
此时recyclebin里有两个test表,查看中两个表的数据个数:
SQL> select count(*) from "BIN$XHwrz1OaQaSeq/NQIE85hw==$0";
COUNT(*)
----------
0
SQL> select count(*) from "BIN$S5L+aNpzQmOScn8VfpJBAA==$0";
COUNT(*)
----------
1000000
假设想要恢复后一张表:
SQL> flashback table "BIN$S5L+aNpzQmOScn8VfpJBAA==$0" to before drop;
Flashback complete.
SQL> select count(*) from test;
COUNT(*)
----------
1000000
假设现在还要恢复前一张表,并重新命名为test2:
SQL> flashback table "BIN$XHwrz1OaQaSeq/NQIE85hw==$0" to before drop rename to t
est2;
Flashback complete.
SQL> select count(*) from test2;
COUNT(*)
----------
0
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26277071/viewspace-713431/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26277071/viewspace-713431/