【Oracle】Drop表后闪回

本文介绍的闪回方式只适用于：删除表的表空间非system，drop语句中没有purge关键字（以上两种情况的误删除操作只能通过日志找回）：

1.删除表后直接从回收站闪回

SCOTT@LGR> drop table emp1;

Table dropped.

SCOTT@LGR> select object_name,original_name,operation from recyclebin where original_name='EMP1';

OBJECT_NAME                     ORIGINAL_NAME  OPERATION                  
------------------------------  -------------  ---------
BIN$QxD9et5lIi3gUwEAAH/pOw==$0  EMP1            DROP                  

SCOTT@LGR> flashback table"BIN$QxD9et5lIi3gUwEAAH/pOw==$0" to before drop;

Flashback complete.

2.当数据库中存在和删除表同名的表，则删除表需要在闪回的同时修改为其他名字，否则闪回失败

SCOTT@LGR> drop table emp1;

Table dropped.

SCOTT@LGR> create table emp1 (id number);

Table created.

SCOTT@LGR> select object_name,original_name,operation from recyclebin
  2  where original_name='EMP1';

OBJECT_NAME                    ORIGINAL_NAME    OPERATION                
------------------------------ --------------
BIN$QxD9et5mIi3gUwEAAH/pOw==$0 EMP1             DROP

SCOTT@LGR> flashback table"BIN$QxD9et5mIi3gUwEAAH/pOw==$0" to before drop;
flashback table"BIN$QxD9et5mIi3gUwEAAH/pOw==$0" to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

SCOTT@LGR> flashback table"BIN$QxD9et5mIi3gUwEAAH/pOw==$0" to before drop rename to emp2;

Flashback complete.