Flashback Features and Undo Space - Oracle 10g

Your Oracle Database includes several features that are based upon undo information and that allow administrators and users to access database information from a previous point in time. These features are part of the overall flashback strategy incorporated into the database and include:

  • Flashback Query
  • Flashback Versions Query
  • Flashback Transaction Query
  • Flashback Table

The retention period for undo information is an important factor for the successful execution of Flashback features. It determines how far back in time a database version can be established. Specifically, you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in. For example, if an application requires that a version of the database be available reflecting its content 12 hours previously, then UNDO _RETENTION must be set to 43200.

You might also want to guarantee that unexpired undo is not overwritten by specifying the RETENTION GUARANTEE clause for the undo tablespace

Flashback Query

Using Oracle Flashback Query feature, users or applications can execute queries as of a previous time in the database. Application developers can use Flashback Query to design an application that allows users to correct their mistakes with minimal DBA intervention. You, as the DBA, need only configure the undo tablespace with an appropriate size and undo retention period. No further action on your part should be required.

The Oracle-supplied DBMS_FLASHBACK package implements Flashback Query at the session level. At the object level, Flashback Query uses the AS OF clause of the SELECT statement to specify the previous point in time for which you wish to view data.

Flashback Versions Query

The Flashback Version Query feature enables users to query the history of a given row. A SELECT statement that specifies a VERSIONS clause returns individual versions of a row between two specified SCNs (system change numbers) or timestamps. The UNDO_RETENTION initialization parameter must be set to a value that is large enough to cover the period of time specified in the VERSIONS clause. Otherwise, not all rows can be retrieved.

The VERSIONS clause can also be used in subqueries of DML and DDL statements.

Flashback Transaction Query

Oracle Database provides a view, FLASHBACK_TRANSACTION_QUERY, that enables you to identify changes made by a particular transaction, or by all transactions issued within a specified period of time. One use for this view could be if a user finds, by using the Flashback Transaction Query feature, that a row value has been changed inappropriately. Querying the FLASHBACK_TRANSACTION_QUERY view can provide specific details of the transaction or transactions that changed the row value.

Flashback Table

The FLASHBACK TABLE statement lets users recover a table to a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. The UNDO_RETENTION initialization parameter must be set to a value that is large enough to cover the period specified in the FLASHBACK TABLE statement.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics