Oracle Flashback Table

　　Ensure that the prerequisites described in Prerequisites for Flashback Table are met.

To use the Flashback Table feature on one or more tables, use the FLASHBACK TABLE SQL statement with a target time or SCN.

You must have the following privileges to use the Flashback Table feature:

You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.

You must have READ or SELECT, INSERT, DELETE, and ALTER privileges on the table.

To flash back a table to a restore point, you must have the SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or the SELECT_CATALOG_ROLE role.

For an object to be eligible to be flashed back, the following prerequisites must be met:

The object must not be included the following categories: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.

The structure of the table must not have been changed between the current time and the target flashback time.

The following Data Definition Language (DDL) operations change the structure of a table: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (except adding a range partition).

Row movement must be enabled on the table, which indicates that rowids change after the flashback occurs.

This restriction exists because if rowids before the flashback were stored by the application, then there is no guarantee that the rowids correspond to the same rows after the flashback. If your application depends on rowids, then you cannot use Flashback Table.

The undo data in the undo tablespace must extend far enough back in time to satisfy the flashback target time or SCN.

The point to which you can perform Flashback Table is determined by the undo retention period, which is the minimal time for which undo data is kept before being recycled, and tablespace characteristics. The undo data contains information about data blocks before they were changed. The flashback operation uses undo to re-create the original data.

To ensure that the undo information is retained for Flashback Table operations, Oracle suggests setting the UNDO_RETENTION parameter to 86400 seconds (24 hours) or greater for the undo tablespace.

1. Connect SQL*Plus to the target database and identify the current SCN.

   You cannot roll back a FLASHBACK TABLE statement, but you can issue another FLASHBACK TABLE statement and specify a time just before the current time. Therefore, it is advisable to record the current SCN. You can obtain it by querying V$DATABASE as follows:

   SELECT CURRENT_SCN FROM V$DATABASE;
   

2. Identify the time, SCN, or restore point to which you want to return the table.

   If you have created restore points, then you can list available restore points by executing the following query:

   SELECT NAME, SCN, TIME FROM V$RESTORE_POINT;
   

3. Ensure that enough undo data exists to rewind the table to the specified target.

   If the UNDO_RETENTION intialization parameter is set, and the undo retention guarantee is on, then you can use the following query to determine how long undo data is being retained:

   SELECT NAME, VALUE/60 MINUTES_RETAINED
   FROM   V$PARAMETER
   WHERE  NAME = 'undo_retention';
   

4. Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table.

   You can enable row movement for a table with the following SQL statement, where table is the name of the table that you are rewinding:

   ALTER TABLE table ENABLE ROW MOVEMENT;
   

5. Determine whether the table that you intend to flash back has dependencies on other tables. If dependencies exist, then decide whether to flash back these tables as well.

   You can issue the following SQL query to determine the dependencies, where schema_name is the schema for the table to be flashed back and table_name is the name of the table:

   SELECT other.owner, other.table_name
   FROM   sys.all_constraints this, sys.all_constraints other
   WHERE  this.owner = schema_name
   AND    this.table_name = table_name
   AND    this.r_owner = other.owner
   AND    this.r_constraint_name = other.constraint_name
   AND    this.constraint_type='R';
   

6. Execute a FLASHBACK TABLE statement for the objects to flash back.

   The following SQL statement returns the hr.temp_employees table to the restore point named temp_employees_update:

   FLASHBACK TABLE hr.temp_employees
     TO RESTORE POINT temp_employees_update;
   

   The following SQL statement rewinds the hr.temp_employees table to its state when the database was at the time specified by the SCN:

   FLASHBACK TABLE hr.temp_employees
     TO SCN 123456;
   

   As shown in the following example, you can also specify the target point in time with TO_TIMESTAMP:

   FLASHBACK TABLE hr.temp_employees
     TO TIMESTAMP TO_TIMESTAMP('2013-10-17 09:30:00', 'YYYY-MM-DD HH:MI:SS');

7. Optionally, query the table to check the data.

 

Keeping Triggers Enabled During Flashback Table

By default, the database disables triggers on the affected table before performing a FLASHBACK TABLE operation. After the operation, the database returns the triggers to the state they were in before the operation (enabled or disabled). To keep triggers enabled during the flashback of the table, add an ENABLE TRIGGERS clause to the FLASHBACK TABLE statement in Step 7.

For example, assume that at 17:00 an HR administrator discovers that an employee is missing from the hr.temp_employees table. This employee was included in the table at 14:00, the last time the report was run. Therefore, someone accidentally deleted the record for this employee between 14:00 and 17:00. The HR administrator uses Flashback Table to return the table to its state at 14:00, respecting any triggers set on the hr.temp_employees table, by using the SQL statement in the following example:

FLASHBACK TABLE hr.temp_employees
  TO TIMESTAMP TO_TIMESTAMP('2013-03-03 14:00:00' , 'YYYY-MM-DD HH:MI:SS') 
  ENABLE TRIGGERS;