How to recover a dropped table in ORACLE

How to recover a dropped table in ORACLE

Use flashback table command to recover dropped table from ORACLE.

Lets observe the following example to recover a table:

First create a sample table:

SQL> create table test_recycle (column1 int);
Table created.

Now, lets check the contents of recyclebin

SQL> show recyclebin;

You may find the dropped objects after executing the above command or it may give no output which means none of the objects is dropped after last purge of recyclebin.

Now, drop the table we created

SQL> drop table test_recycle;
Table dropped.

Check the contents of recyclebin again

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST_RECYCLE     BIN$LkAZkkSCRV6VeiHhANUiHg==$0 TABLE        2013-11-24:11:32:58

See now, the table is in recyclebin. Which means we can recover this table using FLASHBACK TABLE command as follows

SQL> flashback table test_recycle to before drop;
Flashback complete.

Now, check whether table is recovered or not

SQL> desc test_recycle;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COLUMN1                                            NUMBER(38)


This is how we can recover table...


ORA-38305: object not in RECYCLE BIN

This error is because we are trying to recover a table/object which is not in recyclebin.

Lets observe the following example:

Create a test table:
SQL> create table test_purge (column1 int);
Table created.
Now, drop the table using following command which will permanently drop the table from database

SQL> drop table test_purge purge;
Table dropped.

View the contents of recyclebin:
SQL> show recyclebin;

Object wont found in recyclebin

Now, try to recover the table using FLASHBACK BEFORE DROP, which will give the error as follows

SQL> flashback table test_purge to before drop;
flashback table test_purge to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN