Flashback Table

The Flashback Table feature in Oracle Database 10g allows you to revive an accidentally dropped table.

First, let's see the tables in the present schema.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------ ------- ----------
RECYCLETEST TABLE


Now, we accidentally drop the table:


SQL> drop table recycletest;

Table dropped.


Let's check the status of the table now.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE


The table RECYCLETEST is gone but note the presence of the new table
BIN$04LhcpndanfgMAAAAAANPw==$0.

Here's what happened: The dropped table RECYCLETEST, instead of completely disappearing, was renamed to a system-defined name. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$04LhcpndanfgMAAAAAANPw==$0, preserving the complete object structure of the dropped table.

The table and its associated objects are placed in a logical container known as the "recycle bin," which is similar to the one in your PC. However, the objects are not moved from the tablespace they were in earlier; they still occupy the space there. The recycle bin is merely a logical structure that catalogs the dropped objects.

Use the following command from the SQL*Plus prompt to see its content:


SQL> show recyclebin

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


This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:


SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RECYCLETEST TABLE


Voila! The table is reinstated effortlessly. If you check the recycle bin now, it will be empty.

Remember, placing tables in the recycle bin does not free up space in the original tablespace. To free the space, you need to purge the bin using:


PURGE RECYCLEBIN;


But what if you want to drop the table completely, without needing a flashback feature? In that case, you can drop it permanently using:

DROP TABLE RECYCLETEST PURGE;


This command will not rename the table to the recycle bin name; rather, it will be deleted permanently, as it would have been pre-10g.

Other Uses of Flashback Tables

Flashback Drop Table is not limited to reversing the drop of the table. Similar to flashback queries, you can also use it to reinstate the table to a different point in time, replacing the entire table with its "past" version. For example, the following statement reinstates the table to a System Change Number (SCN) 2202666520.

FLASHBACK TABLE RECYCLETEST TO SCN 2202666520;


This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table. To find out how far you can flashback the table, you could use the versioning feature of Oracle Database 10g.