Introduction to Automatic Undo Management - Oracle 10g

This section introduces the concepts of Automatic Undo Management and discusses the following topics:

  • Overview of Automatic Undo Management
  • Undo Retention
  • Retention Guarantee

Overview of Automatic Undo Management

In past releases, when you used the rollback segment method of managing undo space, you were said to be operating in the manual undo management mode. Now, you use the undo tablespace method, and you are said to be operating in the automatic undo management mode. You determine the mode at instance startup using the UNDO _MANAGEMENT initialization parameter. The default value for this parameter is MANUAL. You set it to AUTO to enable automatic undo management.

The following initialization parameter setting causes the STARTUP command to start an instance in automatic undo management mode:


An undo tablespace must be available, into which the database will store undo records. The default undo tablespace is created at database creation, or an undo tablespace can be created explicitly. The methods of creating an undo tablespace are explained in "Creating an Undo Tablespace"

When the instance starts up, the database automatically selects for use the first available undo tablespace. If there is no undo tablespace available, the instance starts, but uses the SYSTEM rollback segment for undo. This is not recommended in normal circumstances, and an alert message is written to the alert file to warn that the system is running without an undo tablespace. ORA-01552 errors are issued for any attempts to write non-SYSTEM related undo to the SYSTEM rollback segment.

If the database contains multiple undo tablespaces, you can optionally specify at startup that you want an Oracle Database instance to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter. For example:

UNDO_TABLESPACE = undotbs_01

In this case, if you have not already created the undo tablespace (in this example, undotbs_01), the STARTUP command will fail. The UNDO_TABLESPACE parameter can be used to assign a specific undo tablespace to an instance in an Oracle Real Application Clusters environment.

The following is a summary of the initialization parameters for automatic undo management mode:

initialization parameters for automatic undo management mode

If the initialization parameter file contains parameters relating to manual undo management, they are ignored.

Undo Retention

Committed undo information normally is lost when its undo space is overwritten by a newer transaction. However, for consistent read purposes, long-running queries sometimes require old undo information for undoing changes and producing older images of data blocks. The success of several Flashback features can also depend upon older undo information.

Automatic Tuning of Undo Retention

Oracle Database 10gautomatically tunes undo retention by collecting database use statistics and estimating undo capacity needs for the successful completion of the queries. You can set a low threshold value for the UNDO_RETENTION parameter so that the system retains the undo for at least the time specified in the parameter, provided that the current undo tablespace has enough space. Under space constraint conditions, the system may retain undo for a shorter duration than that specified by the low threshold value in order to allow DML operations to succeed.

In order to guarantee the success of queries even at the price of compromising the success of DML operations, you can enable retention guarantee. The RETENTION GUARANTEE clause of the CREATE UNDO TABLESPACE and CREATE DATABASE statements ensures that undo information is not overwritten. This option must be used with caution, because it can cause DML operations to fail if the undo tablespace is not big enough. However, with proper settings, long-running queries can complete without risk of receiving the "snapshot too old" message, and you can guarantee a time window in which the execution of Flashback features will ucceed.

Setting the UNDO_RETENTION Initialization Parameter

The default value for the UNDO_RETENTION parameter is 900. Retention is specified in units of seconds. This parameter determines the low threshold value of undo retention. The system retains undo for at least the time specified in this parameter. The setting of this parameter should account for any flashback requirements of the system.

You can set the UNDO_RETENTION parameter initially in the initialization parameter file that is used by the STARTUP process:


You can change the UNDO_RETENTION parameter value at any time using the ALTER SYSTEM statement:


The effect of the UNDO_RETENTION parameter is immediate, but it can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with the "snapshot too old" message.

The amount of time for which undo is retained for Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.

Retention Guarantee

Oracle Database 10g lets you guarantee undo retention. When you enable this option, the database never overwrites unexpired undo data--that is, undo data whose age is less than the undo retention period. This option is disabled by default, which means that the database can overwrite the unexpired undo data in order to avoid failure of DML operations if there is not enough free space left in the undo tablespace.

By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations. Therefore, use caution when using this feature. A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data.

You enable the guarantee option by specifying the RETENTION GUARANTEE clause for the undo tablespace when it is created by either the CREATE DATABASE or CREATE UNDO TABLESPACE statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You do notguarantee that unexpired undo is preserved if you specify the RETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the RETENTION setting for the undo tablespace. A column named RETENTION will contain a value on GUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace).

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 10g Topics