Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Oracle9i – Flashback Query

The above statement sends the session five minutes back in time for the duration of that session or until the EXECUTE dbms_flashback.disable is executed. Oracle recommends that the session not be ended without executing the flashback.disable procedure. I have seen a few sessions ended without executing flashback.disable without any detrimental affects. It is better to be safe than sorry, so the recommendation is to always execute flashback.disable before ending the session.

Currently, flashback query is able to provide 5 days (uptime not wall-clock) worth of data using the date and time parameter. To query data older than this, you must specify an SCN rather than a date and time. There are two important points to remember when using flashback query:

• The current data dictionary is used. If DDL changes have been made to the table between the time stated in the flashback query and the current point in time, an error will be returned.
   

• In Oracle9i Release 1, data cannot be updated during a flashback query enabled session. To save historical data, the old data can be placed into a cursor. The contents of the cursor can be dumped into a work table after the flashback.disable procedure is executed.

In Oracle9i Release 2, the AS OF timestamp clause has been added to the SELECT statement to enable flashback query on a specific table or set of tables. Developers are able to specify the AS OF clause for a single-table, multiple-tables (joins) as well as specify different times for different tables. The AS OF timestamp clause can also be used inside INSERT or CREATE TABLE AS SELECT statements.
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.