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.

7.3.1 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;
```

7.3.2 Performing Flashback Table

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

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

The EMP 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 EMP TO SCN 123456;

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;