Using Oracle9i flashback query   Donald K. Burleson

Before Flashback Query functionality can be used by ordinary users, some actions are required from the database administrators:

Alter system set UNDO_MANAGEMENT=AUTO;

Alter system set UNDO_RETENTION=86400;

Grant execute on DBMS_FLASHBACK to username;

The undo_retention parameters specified (in seconds) the amount of undo images to keep inside Oracle for Flashback queries.  Oracle claims that you can set this value to an entire day (86,400 seconds), and be able to see prior image for a whole day.  Of course, you online undo log must be large enough to hold an entire days worth of undo log data, and for busy shops, that can be many gigabytes.

Uses for Oracle9i flashback are numerous.  Practically every day and end-user of developer seeks to restore data that was lost or modified due to an undesirable application goof.

Oracle claims that you can use flashback query to create applications with built-in end user self-service error correction capabilities, but few Oracle shops have attempted to implement self-service correction and normally use the DBA for flashback recovery.

Flashback Query is enabled and disabled using the DBMS_FLASHBACK package. The point in time of the flashback can be specified using the SCN or the actual time.

Using a simple example, suppose you made an inappropriate change to the database at 11:15 AM.  You could use the following command to tell Oracle to apply all undo log images as of 11:00 AM:

EXECUTE dbms_Flashback.Enable_At_Time('28-AUG-02 11:00:00');

Now, all of your queries will show the data as it existed at 11:00AM and you can easily recover your lost or modified data.

While this is a great feature for going back in time, there are some restrictions:

• The server must be configured to use Automatic Undo Management.

• No DDL or DML can be issued when using Flashback Query.

• Flashback does not undo DDL operations such as DROP commands

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: