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 4:25 AM, an employee ‘MILLER’ had been deleted from your EMP table,
and you know that at 4:20AM 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 ‘MILLER’ at 4:20AM, May 20, 2020:
SELECT * FROM EMP AS OF TIMESTAMP
   TO_TIMESTAMP(‘2020-05-20 04:20:00’, ‘YYYY-MM-DD HH:MI:SS’)
   WHERE ename = ‘MILLER’;
Restoring MILLER information to the table EMP requires the following update:
INSERT INTO EMP
(SELECT * FROM EMP AS OF TIMESTAMP
   TO_TIMESTAMP(‘2020-05-20 04:20:00’, ‘YYYY-MM-DD HH:MI:SS’)
   WHERE ename = ‘MILLER’);
Recent Posts

Start typing and press Enter to search