Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-03 |
|
|
View PDF |
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.
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.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 theFLASHBACK
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;
See Also:
Oracle Database SQL Reference for a simple Flashback Table scenario