【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.