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
TABLE SQL statement with a target time or SCN.
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
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.
DROPis 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.
The following SQL*Plus statement performs a
FLASHBACK TABLE operation on the table
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
TABLEstatement, 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 a
FLASHBACK 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;
See Also:Oracle Database SQL Reference for a simple Flashback Table scenario