7.3 Oracle Flashback Table: Returning Individual Tables to Past States

Oracle Flashback Table provides the DBA the ability to recover a table or set of tables to a specified point in time in the past very quickly, easily, and without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining associated attributes such as current indexes, triggers and constraints, and not requiring the DBA to find and restore application-specific properties. Using Flashback Table causes the contents of one or more individual tables to revert to their state at some past SCN or time.

Flashback Table uses information in the undo tablespace to restore the table. You do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.

For more information on Automatic Undo Management, see Oracle Database Administrator's Guide.

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

7.3.1 Prerequisites for Using Flashback Table

The prerequisites for using the Flashback Table feature on a table are as follows:

Row movement must be enabled on the table. You can enable row movement with the following SQL statement:
```
ALTER TABLE table ENABLE ROW MOVEMENT;
```
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 SELECT, INSERT, DELETE, and ALTER privileges on the table.
Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.

Note:

FLASHBACK TABLE... TO BEFORE DROP is a use of the Flashback Drop feature, not Flashback Table, and therefore is not subject to these prerequisites. See "Oracle Flashback Drop: Undo a DROP TABLE Operation""Oracle Flashback Drop: Undo a DROP TABLE Operation" for more information.

7.3.2 Performing Flashback Table

The following SQL*Plus statement performs a FLASHBACK TABLE operation on the table EMP:

The EMP table is restored to its state when the database was at the time specified by the SCN.

FLASHBACK TABLE EMP TO SCN 123456;

You can also specify the target point in time for the FLASHBACK TABLE operation using TO_TIMESTAMP. For example:

FLASHBACK TABLE EMP TO TIMESTAMP 
      TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')

Note:

The mapping of timestamps to SCNs is not always exact. When using timestamps with the FLASHBACK TABLE statement, the actual point in time to which the table is flashed back can vary by up to approximately three seconds of the time specified for TO_TIMESTAMP. If an exact point in time is required, use an SCN rather than a time expression.

By default, the database disables triggers on the affected table before performing aFLASHBACK TABLE operation, and after the operation returns them to the state they were in before the operation (enabled or disabled). If you wish for triggers on a table to apply during FLASHBACK TABLE, then add an ENABLE TRIGGERS clause to the FLASHBACK TABLE statement:

FLASHBACK TABLE table_name 
        TO TIMESTAMP timestamp ENABLE TRIGGERS;

The following scenario is typical of the kind of logical corruption where Flashback Table could be used:

At 17:00 an HR administrator discovers that an employee JOHN is missing from the EMP 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 JOHN between 14:00 and the present time. She uses Flashback Table to return the table to its state at 14:00, respecting any triggers set on the EMP table, using the SQL statement shown in this example:

FLASHBACK TABLE EMP 
        TO TIMESTAMP TO_TIMESTAMP('2005-03-03 14:00:00') ENABLE TRIGGERS;