查看回收站功能是否开启（默认是开启的）：
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博客 ” ，链接：https://blog.itpub.net/28497416/viewspace-2124749/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
降低高水位线的方法
请登录后发表评论
登录
全部评论
