Example: Recovering Data with Flashback Query

This example uses a Flashback Query to examine the state of a table at a previous time. Suppose that you discover at 12:30 p.m. that the row for employee Chung was deleted from the employees table. You also know that at 9:30 a.m. the data for Chung was correctly stored in the database. You can use a Flashback Query to examine the contents of the table at 9:30 a.m. to find out what data was lost. If appropriate, you can then reinsert the lost data.

Example: Retrieving a Row with Flashback Query retrieves the state of the record for Chung at 9:30 a.m., April 4, 2005.

Retrieving a Row with Flashback Query

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

The update in Example: Reinserting a Row After a Flashback Query restores Chung's information to the employees table.

Reinserting a Row After a Flashback Query

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