Although the RECOVER TABLE command is a nice enhancement, I recommend that if you have an accidentally dropped table, you first explore using the recycle bin or Flashback Table to Before Drop feature to restore the table. Or, if the table was erroneously deleted from, then use the Flashback Table feature to restore the table to a point in time in the past.

It might even be possible to restore from a FLASHBACK QUERY using CTAS (create table as). If neither of the prior options is viable, then consider using the RMAN Recover Table feature.

Using FLASHBACK QUERY has saved me from several issues, and it really is my first point to go to with any application or data issue where something has been changed or deleted. Saving the table at that point in time allows for validation and getting the system back to where it needs to be.

Flashing Back a Table

To simplify the recovery of an accidentally dropped table, Oracle introduced the Flashback Table feature. Oracle offers two different types of Flashback Table operations:

•     FLASHBACK TABLE TO BEFORE DROP quickly undrops a previously dropped table. This feature uses a logical container named the recycle bin.

•     FLASHBACK TABLE flashes back to a recent point in time to undo the effects of undesired DML statements. You can flash back to an SCN, a timestamp, or a restore point.

Oracle introduced FLASHBACK TABLE TO BEFORE DROP to allow you to quickly recover a dropped table.

When you drop a table, if you do not specify the PURGE clause, Oracle does not drop the table—instead, the table is renamed.

Any tables you drop (that Oracle renames) are placed in the recycle bin. The recycle bin provides you with an efficient way to view and manage dropped objects.

To use the Flashback Table feature, you do not need to implement an FRA, nor do you need Flashback Database to be enabled.

The FLASHBACK TABLE TO BEFORE DROP operation works only if your database has the recycle bin feature enabled (which it is by default).

You can check the status of the recycle bin, as follows:

SQL> show parameter recyclebin

NAME          TYPE                VALUE

recyclebin           string                         on

FLASHBACK TABLE TO BEFORE DROP

Here is an example. Suppose the INV table is accidentally dropped: SQL> drop table inv;

Verify that the table has been renamed by viewing the contents of the recycle bin:

SQL> show recyclebin;

ORIGINAL NAME  RECYCLEBIN NAME   OBJECT TYPEDROP TIME

INV    BIN$0zIqhEFlcprgQ4TQTwq2uA==$0 TABLE

The SHOW RECYCLEBIN statement shows only tables that have been dropped. To get a more complete picture of renamed objects, query the RECYCLEBIN view:

SQL> select object_name, original_name, type from recyclebin;

Here is the output:

OBJECT_NAME     ORIGINAL_NAM TYPE

BIN$0zIqhEFjcprgQ4TQTwq2uA==$0

BIN$0zIqhEFkcprgQ4TQTwq2uA==$0

BIN$0zIqhEFlcprgQ4TQTwq2uA==$0

INV_PK INV_TRIG INV

INDEX TRIGGER TABLE

In this output, the table also has a primary key that was renamed when the object was dropped. To undrop the table, do this:

SQL> flashback table inv to before drop;

The prior command restores the table to its original name. This statement, however, does not restore the index to its original name:

SQL> select index_name from user_indexes where table_name=’INV’; INDEX_NAME

BIN$0zIqhEFjcprgQ4TQTwq2uA==$0

In this scenario, you have to rename the index:

SQL> alter index “BIN$0zIqhEFjcprgQ4TQTwq2uA==$0” rename to inv_pk;

You also have to rename any trigger objects in the same manner. If referential constraints were in place before the table was dropped, you must manually re-create them.

If, for some reason, you need to flash back a table to a name different from the original name, you can do so as follows:

SQL> flashback table inv to before drop rename to inv_bef;