Flashback Row (10g)

The ability to flashback transactions to a point in time is quite a handy feature. Here is how it all works ....

First of all, the oracle init.ora parameter db_flashback_retention_target needs to be set. This parameter specifies the upper limit (in minutes) on how far back in time the database may be flashed back. The default value is 1440 minutes (24 hours).

How far back one can flash back a database depends on how much flashback data Oracle has kept in the recovery area.

Retrieving a Row with Flashback Query

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting
Michael              Clarke
Stuart               Clark
Brad                 Haddin
Brett                Lee

5 rows selected.

Delete a row of data - make a note of the deletion time for the purpose of this exercise.

SQL> DELETE FROM AUSTRALIA_CRICKET WHERE FIRST_NAME='Brett';

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              

4 rows selected.

Now to use a flashback query to view the data as it was prior to the delete.

SQL> SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI');


FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              
Brett                Lee                 

5 rows selected.

You may restore the deleted row as follows

SQL> INSERT INTO AUSTRALIA_CRICKET
     (SELECT * FROM AUSTRALIA_CRICKET AS OF TIMESTAMP TO_TIMESTAMP('2009-08-27 15:26','YYYY-MM-DD HH24:MI') 
       WHERE FIRST_NAME='Brett');

1 row inserted.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME           
-------------------- --------------------
Ricky                Ponting             
Michael              Clarke              
Stuart               Clark               
Brad                 Haddin              
Brett                Lee                 

5 rows selected.