With Oracle, how can I restore a table that I dropped accidentally?

Question

I have actually dropped instead of deleting a table in Oracle SQL.

Drop table emp;

Is there any way to retrieve it back?

Answer 1

Actually there exists a way to retrieve back the dropped table. Please find the steps below. When you drop a table, the database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, it is placed in the Recycle Bin of the database. The Flashback Drop operation recovers the table from the recycle bin.

Also please check if you are using oracle 10g and above too.

SQL> drop table vimal;

 Table dropped.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
VIMAL            BIN$c9/MeUSERvCmafRSweHlWQ==$0 TABLE        2017-01-   06:16:57:29

SQL> flashback table "BIN$c9/MeUSERvCmafRSweHlWQ==$0" to before drop;

Flashback complete.

SQL> select * from vimal;

NAME               ID
---------- ----------
f                   1

I request you to please read the oracle documentation for further clarification. Please go through them.

Reference can be taken from : https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm