Prior to Oracle 10g, if a table was accidentally dropped, you had to do the following to restore the table:
- Restore a backup of the database to a test database.
- Perform an incomplete recovery up to the point in time at which the table was dropped.
- Export the table.
- Import the table into the production database.
This process can be time-consuming and resource intensive. It requires extra server resources as well as time and effort from a DBA.
To simplify 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. As of Oracle 10g, when you drop a table, if you don’t specify the PURGE clause, Oracle doesn’t 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.
Note To use the Flashback Table feature, you don’t need to implement an FRA, nor do you need Flashback Database to be enabled.
The FLASHBACK TABLE TO BEFORE DROP operation only works 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
When you drop a table, if you don’t specify the PURGE clause, Oracle renames the table with a system-generated name. Because the table isn’t really dropped, you can use FLASHBACK TABLE TO BEFORE DROP to instruct Oracle to rename the table with its original name. 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 TYPE DROP TIME
---------------- ------------------------------ ------------ -----------
INV BIN$BCRjF6KSbi/gU7fQTwrP+Q==$0 TABLE 2014-09-28:11:26:15
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_NAME TYPE
---------------------------------------- -------------------- ----------
BIN$BCRjF6KSbi/gU7fQTwrP+Q==$0 INV TABLE
BIN$BCRjF6KRbi/gU7fQTwrP+Q==$0 INV_TRIG TRIGGER
BIN$BCRjF6KQbi/gU7fQTwrP+Q==$0 INV_PK INDEX
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, doesn’t restore the index to its original name:
SQL> select index_name from user_indexes where table_name='INV';
INDEX_NAME
---------------------------------
BIN$BCRjF6KQbi/gU7fQTwrP+Q==$0
In this scenario, you have to rename the index:
SQL> alter index "BIN$BCRjF6KQbi/gU7fQTwrP+Q==$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;Flashing Back a Table to a Previous Point in Time
If a table was erroneously deleted from, you have the option of flashing back the table to a previous point in time. The Flashback Table feature uses information in the undo tablespace to restore the table. The point in time in the past depends on your undo tablespace retention period, which specifies the minimum time that undo information is kept.
If the required flashback information isn’t in the undo tablespace, you receive an error such as this:
ORA-01555: snapshot too oldIn other words, to be able to flash back to a point in time in the past, the required information in the undo tablespace must not have been overwritten.
Suppose you’re testing an application feature, and you want to quickly restore a table back to a specific SCN. As part of the application testing, you record the SCN before testing begins:
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4760099
You perform some testing and then want to flash back the table to the SCN previously recorded. First, ensure that row movement is enabled for the table:
SQL> alter table inv enable row movement;
SQL> flashback table inv to scn 4760089;
The table should now reflect transactions that were committed as of the historical SCN value specified in the FLASHBACK statement.
You can also flash back a table to a prior point in time. For example, to flash back a table to 15 minutes in the past, first enable row movement, and then use FLASHBACK TABLE:
SQL> alter table inv enable row movement;
SQL> flashback table inv to timestamp(sysdate-1/96) ;
The timestamp you provide must evaluate to a valid format for an Oracle timestamp. You can also explicitly specify a time, as follows:
SQL> flashback table inv to timestamp
to_timestamp('14-jan-15 12:07:33','dd-mon-yy hh24:mi:ss');
FLASHBACK TABLE TO RESTORE POINT
A restore point is a name associated with a timestamp or an SCN in the database. You can create a restore point that contains the current SCN of the database, as shown:
SQL> create restore point point_a;Later, if you decide to flash back a table to that restore point, first enable row movement:
SQL> alter table inv enable row movement;
SQL> flashback table inv to restore point point_a;
The table should now contain transactions as they were at the SCN associated with the specified restore point.
