查看回收站功能是否开启(默认是开启的):
show parameter recyclebin;
开启或关闭回收站
alter system set recyclebin=on scope=spfile;
(或者) alter session set recyclebin=on scope=spfile;
查看回收站里的内容
show recyclebin; (普通用户下)
(或者)select * from user_recyclebin;
反复创建删除同样表名的表时:
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE ,CREATETIME,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQTrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-08:15:19:18 2016-09-09:10:19:46
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
BIN$PArGlFQVrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-09:10:20:48 2016-09-09:10:21:08
show parameter recyclebin;
开启或关闭回收站
alter system set recyclebin=on scope=spfile;
(或者) alter session set recyclebin=on scope=spfile;
查看回收站里的内容
show recyclebin; (普通用户下)
(或者)select * from user_recyclebin;
反复创建删除同样表名的表时:
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE ,CREATETIME,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQTrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-08:15:19:18 2016-09-09:10:19:46
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
BIN$PArGlFQVrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-09:10:20:48 2016-09-09:10:21:08
可以看出T2表在不同时间被创建两次,并且均被删除。
闪回T2表至被删除前:
SQL> flashback table t2 to before drop;
Flashback complete.
重新查询一次,可以发现默认被闪回的是最近被删除掉的T2表,而不是最早被删除的T2表。
闪回T2表至被删除前:
SQL> flashback table t2 to before drop;
Flashback complete.
重新查询一次,可以发现默认被闪回的是最近被删除掉的T2表,而不是最早被删除的T2表。
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE ,CREATETIME,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQTrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-08:15:19:18 2016-09-09:10:19:46
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
如果想要指定闪回最早删除的T2表,需要指定表名闪回:
SQL> drop table t2;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE ,CREATETIME,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQTrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-08:15:19:18 2016-09-09:10:19:46
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
BIN$PArGlFQWrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-09:10:20:48 2016-09-09:10:42:07
SQL> flashback table "BIN$PArGlFQTrzfgU5gCqMD/Lw==$0" to before drop rename to t2_test;
Flashback complete.
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE ,CREATETIME,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
BIN$PArGlFQWrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-09:10:20:48 2016-09-09:10:42:07
清空回收站
SQL>purge recyclebin;
单个对象清除
SQL> purge table "BIN$PArGlFQSrzfgU5gCqMD/Lw==$0";
Table purged.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$PArGlFQWrzfgU5gCqMD/Lw==$0 TABLE 2016-09-09:10:42:07
T3 BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 TABLE 2016-09-09:10:19:51
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQTrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-08:15:19:18 2016-09-09:10:19:46
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
如果想要指定闪回最早删除的T2表,需要指定表名闪回:
SQL> drop table t2;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE ,CREATETIME,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQTrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-08:15:19:18 2016-09-09:10:19:46
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
BIN$PArGlFQWrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-09:10:20:48 2016-09-09:10:42:07
SQL> flashback table "BIN$PArGlFQTrzfgU5gCqMD/Lw==$0" to before drop rename to t2_test;
Flashback complete.
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE ,CREATETIME,DROPTIME from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME
------------------------------ -------------------------------- ------------------------- ------------------- -------------------
BIN$PArGlFQSrzfgU5gCqMD/Lw==$0 ZHONG TABLE 2016-09-09:10:08:54 2016-09-09:10:19:27
BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 T3 TABLE 2016-09-08:15:11:37 2016-09-09:10:19:51
BIN$PArGlFQWrzfgU5gCqMD/Lw==$0 T2 TABLE 2016-09-09:10:20:48 2016-09-09:10:42:07
清空回收站
SQL>purge recyclebin;
单个对象清除
SQL> purge table "BIN$PArGlFQSrzfgU5gCqMD/Lw==$0";
Table purged.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$PArGlFQWrzfgU5gCqMD/Lw==$0 TABLE 2016-09-09:10:42:07
T3 BIN$PArGlFQUrzfgU5gCqMD/Lw==$0 TABLE 2016-09-09:10:19:51
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28497416/viewspace-2124749/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28497416/viewspace-2124749/