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