Flashback Drop – 2 Tables with the same name.

Flashback drop allows you to restore a table without having to perform a point-in-time recovery. In 10G the DROP DDL command has been changed internally to a rename the table and its associated objects (excluding foreign key constraints). Its a feature that I personally never have had to use. Although, leaving this feature enabled did cause some performance issues for me. (See: Previous blog posts about the Recycle bin feature.)

The basic syntax for restoring a dropped table is simply:

SQL> flashback table <tablename> to before drop;
Flashback complete.

Simple enough. But what if since the table was dropped, another table was created with the same name? By default, Flashback drop restores the most recent version of the table. In order to restore a previous copy you need to specify the recycle bin name. For example:

SQL> drop table EMP;

Table dropped.

SQL> create table EMP (name varchar2(50));

Table created.

SQL> insert into EMP values ('John Doe');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

NAME
--------------------------------------------------------------------------------
John Doe

SQL> drop table emp;

Table dropped.

SQL> select object_name, original_name, type, droptime from user_recyclebin;

OBJECT_NAME
--------------------------------------------------------------------------------
ORIGINAL_NAME
--------------------------------------------------------------------------------
TYPE
---------------------------------------------------------------------------
DROPTIME
---------------------------------------------------------
BIN$Yr7gB0huGergRAADuvZ6hQ==$0
EMP
TABLE
2009-02-12:14:39:14

BIN$Yr7gB0hvGergRAADuvZ6hQ==$0
EMP
TABLE
2009-02-12:14:39:53


SQL> flashback table "BIN$Yr7gB0huGergRAADuvZ6hQ==$0" to before drop;

Flashback complete.

SQL> desc emp;
Name Null? Type
---------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)