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

Question

I actually dumped, not deleted the table in Oracle SQL.

Drop table emp; 

Is there any way to get it back?

Answer 1

Actually, there is a way to get back a deferred table. Below you will find instructions. When you delete a table, the database does not immediately delete the space associated with the table. Instead, the table is renamed and, together with any related objects, is placed in the database Recycle Bin. The Flashback Drop operation restores a table from the recycle bin.

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

 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 

Please read the oracle documentation for further clarification. Go through them.

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

Answer 2

Recovering a deleted table is easy in Oracle, provided that the table has not been deleted with the PURGE option. If a table is dropped and the space occupied by this table is freed, and the table is not moved to the trash. But if the table is disabled without the PURGE option, Oracle has this very neat feature - a recycle bin, similar to a recycle bin in Windows. There are two kinds of bin-bin in Oracle: USER_RECYCLEBIN and DBA_RECYCLEBIN, the synonym RECYCLEBIN points to your USER_RECYCLEBIN.

http://elena-sqldba.blogspot.in/2013/01/how-to-retrieve-dropped-table-in-oracle.html

http://www.dba-oracle.com/t_recover_dropped_table.htm

Answer 3

Use this:

 select object_name, original_name, type from recyclebin; 

Although the remote table has been renamed, it saves its data, you can easily "lift" the table using flashback .

 flashback table yourTableName to before drop;