Flashback Query

Anyone ever encounter some “oh my god” or “I’m in deep shit” situations when you did a batch update to the data?

Instead of recover the data from backup. We have something call flash back query from Oracle 9i onward. With this feature you are able to query out the version of data “as of” specific timestamp.

Command Example:

1. SQL> SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON';

2. SQL> INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON');


P/S: This feature is of available in Oracle 9i onward. You must make sure your UNDO_RETENTION value is big enough, else you will get "ora-01555 snapshot too old" error

Related topic: Oracle Flashback Technologies includes Flashback Database, Flashback Table, Flashback Drop, Flashback Versions Query, and Flashback Transaction Query.