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. This provides significant benefits over media recovery in terms of ease of use, availability and faster restoration of data.

Prerequisites for Using Flashback Table

The prerequisites for performing a FLASHBACK TABLE operation are as follows:

* 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.
* Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

ALTER TABLE table ENABLE ROW MOVEMENT;

Performing Flashback Table

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

FLASHBACK TABLE employee TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');

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

You can also specify the target point in time for the FLASHBACK TABLE operation using an SCN:

FLASHBACK TABLE employee TO SCN 123456;

The default for a FLASHBACK TABLE operation is for triggers on a table to be disabled. The database disables triggers for the duration of the operation, and then returns them to the state that they were in before the operation was started. If you wish for the triggers to stay enabled, then use the ENABLE TRIGGERS clause of the FLASHBACK TABLE statement, as shown in this example:

FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03 12:05:00' 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 EMPLOYEE table. This employee was present at 14:00, the last time she ran a report. 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, as shown in this example:

FLASHBACK TABLE EMPLOYEES TO TIMESTAMP
TO_TIMESTAMP('2003-04-04 14:00:00','YYYY-MM-DD HH:MI:SS')
ENABLE TRIGGERS;

Source: http://www.stanford.edu