Flashback table to before drop

1. Count the number of rows in a table emp before dropping it

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select * from  user_recyclebin;

no rows selected

2. Drop the table emp

SQL> drop table emp ;

Table dropped.

SQL> select count(*) from emp;
select count(*) from emp
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

3.Check the table is in the recyclebin

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

OBJECT_NAME                    ORIGINAL_NAME                    TYPE                      DROPTIME            CAN
------------------------------ -------------------------------- ------------------------- ------------------- ---
BIN$+2V6ulv0c4vgQ28AqMDYBQ==$0 PK_EMP                           INDEX                     2014-06-09:15:45:52 NO
BIN$+2V6ulv1c4vgQ28AqMDYBQ==$0 EMP                              TABLE                     2014-06-09:15:45:52 YES

4.flashback the table emp to before drop

SQL> flashback table emp to before drop;

Flashback complete.

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

no rows selected

5.The table emp is flashbacked

SQL> SELECT COUNT(*) FROM EMP;

  COUNT(*)
----------
        14

6.The dependent objects are flashbacked but with not their original name,we need to rename the constraints to their original name.

SQL> select constraint_name, constraint_type from user_constraints where table_name='EMP';

CONSTRAINT_NAME                C
------------------------------ -
BIN$+2V6ulvzc4vgQ28AqMDYBQ==$0 P

SQL> ALTER TABLE EMP RENAME CONSTRAINT "BIN$+2V6ulvzc4vgQ28AqMDYBQ==$0" TO pk_emp;

Table altered.