Flashback Drop - Procedure

In Oracle 10g, when you drop a table, the table is not really dropped. Rather, the table is renamed. The data in the table is still available. You can use the Flashback Drop technologies to recover an accidentally dropped table. The procedure is as follows,


Step 1: Check whether the dropped table is still available in the recycle bin, command.

User level

Scott@prod> show recyclebin;

Database level

dba@prod> Select * from dba_recyclebin;

The original_name column shows the dropped table’s original name. It also has the recycle bin’s name and the dropped time.

Step 2: Use the Flashback Drop command to recover the table

Scott@prod> Flashback Table Employees to before drop [Rename to New_Employees];

Or you can use the system generated name as follows

Scott@prod> Flashback Table “BIN$YkyfmFKl8MLgQwppGAzwwg==$0" to before drop [Rename to New_Employees];

Step 3: To permanently drop the table.

Scott@prod> Drop Table Employees purge;

Scott@prod> purge dba_recyclebin;