Recover Dropped Table Using FLASHBACK Feature in Oracle 10g

Step 1 : We are going to drop table EMP from SCOTT Schema

SQL> CONN SCOTT/TIGER

SQL> DROP TABLE EMP;
Table dropped.

Step2 : Check that EMP table was dropped

SQL> SELECT * FROM EMP;
SELECT * FROM EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now We are confirmed that table has been dropped.

Step3 : Check whether the table is available IN RECYCLEBIN

SQL> SELECT OBJECT_NAME, ORIGINAL_NAME FROM USER_RECYCLEBIN;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$T0xRBK9YSomiRRmhwn/xPA==$0 EMP


Step 4 : Use the FLASHBACK Command to recover the dropped table

SQL> FLASHBACK TABLE EMP TO BEFORE DROP;

Flashback complete.

SQL> SELECT EMPNO FROM EMP;

EMPNO
-----------
7369
7499
7521
7566
7654
7698

6 rows selected.

If the dropped table is not available in RECYCLEBIN then you can’t use the FLASHBACK Command to recover it,you will need to restore the table from a valid backup.