Flashback

Oracle Flashback

Oracle Flashback allows you to query the database as of a specific time or System Change Number (SCN) in the past. It can be useful in recovering from a recent logical or user error – just use Flashback to query the data prior to an accidental data deletion, for example.

To use Flashback you must have already:

• Set the initialization parameter UNDO_MANAGEMENT = AUTO

(This enables a new 9i feature called Automatic Undo Management, for Oracle-controlled management of rollback segments.)

• Set the initialization parameter UNDO_RETENTION to how far back (in seconds) Oracle should retain undo information:

Example: ALTER SYSTEM SET UNDO_RETENTION = 1600 ;

• Have the execute privilege on system package DBMS_FLASHBACK

Here’s how to use Flashback:

• Run either DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER to enable Flashback for the session and indicate the time or SCN to return to
• Perform the query and access missing information by a PL/SQL cursor. You can not use a regular DML query for this purpose!
• Disable Flashback when done by running DBMS_FLASHBACK.DISABLE

A primary limitation in using Flashback is how much extra Undo information you want to store, and how much space you are willing to allocate to this purpose.