With Oracle, how can I restore a table that I dropped accidentally?
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
Use this:
select object_name, original_name, type from recyclebin;
Although the dropped table is renamed, it retains its data, you can easily "undrop" the table by using the flashback
.
flashback table yourTableName to before drop;