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.
Oracle Database Application Developer's Guide - Fundamentals for a more extensive discussion of the use of the
AS OF SQL statement and extensive examples of its use.
Oracle Database SQL Reference for more details on the syntax of the
OF form of the