Flashback Drop/Flashback Table

Using Flashback Drop
Flashback drop is the process of saving a copy of the dropped database object and dependent objects in the Recycle Bin so that these objects can be recovered if necessary. The dropped database object is not removed from the database until the Recycle Bin is emptied. This provides a mechanism for the user to recover an accidental drop of a table. In addition, a Flashback Drop does not impact other users in the database to restore a dropped table, whereas incomplete recovery has database-wide impacts because there may be multiple database objects involved in a tablespace or datafile.

SQL> flashback table t1 to before drop;

Flashback Drop is designed to temporarily store the dropped object and dependent objects for a period of time, which can be seen in Recycle Bin.

In order to query the object in the Recycle Bin, you must have the privileges. You also need the FLASHBACK privilege.

SQL> flashback table “BIN$9GjGHKB754HGC==$0” to before drop rename to t2;
SQL> select * from t2;

Using Flashback Table
Flashback Table is a Flashback Technology that allows you to recover a table or set of tables to a specific point-in-time without performing an incomplete recovery. All dependent objects are also recovered when using Flashback Table. Benefits over incomplete recovery:

• Much faster and easier to use
• Flashback Table does not impact the availability of the database
• DBA is not required to perform Flashback Table, so users can quickly recover from logical corruptions

Like other Flashback Technologies, Flashback Table is based on undo data. The privilege required to use Flashback Table is the system privilege FLASHBACK ANY TABLE or FLASHBACK TABLE. You must also grant SELECT, INSERT, DELETE, and ALTER object privileges to user performing Flashback Table.

There are two main clauses that are used with Flashback Table:

• TO SCN clause
• TO TIMESTAMP clause

NOTE: Flashback Table must have ROW MOVEMENT enabled with the following command: ALTER TABLE table_name ENABLE ROW MOVEMENT.

Ex:
SQL> alter table t1 enable row movement;
SQL> select current_scn from v$database;
SQL> update t1 set salary=500000 where employee=’jones’;
SQL> commit

SQL> flashback table t1 to scn 1071333;

NOTE: Triggers are disabled by default during Flashback Table process. Triggers can be enabled with ENABLE TRIGGERS option on the FLASHBACK TABLE command.
SQL> flashback table table_name to scn 168879 enable triggers;