Oracle's Disappearing Act: Flashback Query

Did you ever find yourself in a situation where you accidently deleted a number of records which you shouldn’t have? And to top it off, committed the changes? I did. And I can tell you, it doesn’t give you a nice warm fuzzy feeling inside. A day’s worth of log messages gone, and this was invaluable information.
But what to do?

Back in the days before Oracle 9i (release 1), there was not a whole lot you could do, than to ask the DBA to restore this data. Thankfully I attended the Oracle 7Up workshop and remembered a way restore the records without calling our DBA.

The Flashback Query lets you query data from the past. Even though the records that were accidently deleted and commited, this data could still be queried. In order to restore this information it was a simple insert and commit.

To demonstrate this functionality I created a sample script. This scripts creates a table which has date column in it. About 25% of the records will have yesterdays date. These records will perform the “disappearing act”.

In the script there is a delay of 10 minutes. This will allow SMON to record a System Change Number (SCN). This recording of the SCN is done every 5 minutes since the startup of the database.

Delete and commit all records which have a “created” date of yesterday. That’s about 25% of all records. Normally this would be the moment to have a slight panic attack, you realize you deleted and committed records you shouldn’t have. It’s time to flashback to happier times. The time when all the records were still there and all was well. 😀

insert into t
   select *
     from t as of timestamp (systimestamp - interval '1' minute)
    where Trunc (created) = Trunc (sysdate) -1

In the Oracle 10g , the Flashback functionality is even more enhanced. Here you can even undrop a table!

More information on Flashback Query:
Oracle 7Up Workshop (Dutch)

Setting Up the Database for Flashback Query

Oracle 10g Concepts Guide: Overview of Oracle Flashback Query