7.2 Oracle Flashback Query: Recovering at the Row Level

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMP table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2005:

SELECT * FROM EMP AS OF TIMESTAMP 
   TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE name = 'JOHN';

Restoring John's information to the table EMP requires the following update:

INSERT INTO EMP 
    (SELECT * FROM EMP AS OF TIMESTAMP 
     TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.