Flashback Table Drop

• Flashback Drop reverses the effect of a DROP TABLE operation.
• When a table is dropped, it will be renamed, and placed in the Recycle Bin.
• The Recycle Bin utilizes tablespace where table is on.
• The Recycle Bin objects are counted against a user’s quota.
• Oracle will automatically claim the Recycle Bin space if there is storage constraint.
• Dropped table is renamed and placed in the Recyclebin.

SQL> drop table TESTTABLE;
Table dropped.

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

• To view the contents in the Recyclebin.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ ------------------
TESTTABLE        BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE        2004-02-16:21:13:31

• Restore the table using Flashback table. The table with the original name will be restored.

SQL> FLASHBACK TABLE TESTTABLE TO BEFORE DROP;
FLASHBACK COMPLETE.

• Optionally, if name change is needed, for example, there is an existing table name already, the option RENAME TO <TABLENAME> can be used.

SQL> FLASHBACK TABLE TESTTABLE TO BEFORE DROP RENAME TO TESTTABLE1;
FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TESTTABLE                      TABLE

• To free up Recyclebin

SQL> PURGE RECYCLEBIN;

• To permanently drop table

SQL> DROP TABLE TESTTABLE PURGE;