DBA Tips Archive for Oracle


Automatic UNDO Management in Oracle9i

by Jeff Hunter, Sr. Database Administrator


  1. Introduction
  2. Benefits of Using Automatic Undo Management
  3. Special Notes About Using AUM
  4. Automatic Undo Management and Real Application Clusters (RAC)
  5. The SYSTEM Rollback Segment
  6. Depreciating the Use of Rollback Segments
  7. Creating the UNDO Tablespace
  8. Dropping an UNDO Tablespace
  9. Configuring the Instance for Automatic UNDO Management
  10. Using the undo_tablespace Parameter
  11. Using the undo_suppress_errors Parameter
  12. UNDO Retention Period
  13. The Data Dictionary and Automatic UNDO Management


Transactions in Oracle can be either committed or rolled back. In order to prepare for the potential rollback of a transaction, Oracle needs to store the before images of the data. Oracle also stores pre-images of data to:

In databases prior to release 9i, this undo information was maintained in special segments called ROLLBACK SEGMENTS. DBAs would spend a significant amount of time with maintenance and tuning issues with rollback segments that included:

Oracle9i greatly simplified the space management and tuning issues related to rollback segments with the introduction of Automatic Undo Management (AUM). To use the Oracle9i Automatic UNDO Management features, you must first create an UNDO tablespace that has adequate space for the undo (rollback) segment creation. You must then configure the Oracle9i instance to use the Oracle9i Automatic UNDO Management feature.

The following article provides an overview of Oracle's new Automatic Undo Management (AUM) features introduced with Oracle9i. The details of how to configure your Oracle database to take advantage of this great new feature will also be discussed.

Benefits of Using Automatic Undo Management

Two of the key benifits to using AUM are:

With AUM, Oracle creates, extends, and allocates undo (rollback) segments within the space available in the UNDO tablespace. AUM eliminates the need for the DBA to explicitly assign large rollback segments for batch transactions and for you to be concerned with running out of space when different kinds of transactions occur concurrently. It is important to keep in mind that Oracle still uses rollback segments for storing undo information, but those segments are created and managed by Oracle.

Using AUM, the DBA is also able to specify the retention period for the undo information in the segment before it is overwritten. This feature gives the DBA control over the chance of getting the snapshot too old error.

Special Notes About Using AUM

This section attempts to pull together some thoughts to consider when using AUM.

  1. If you want to manage rollback segments manually, as DBAs had to in Oracle7, Oracle8, and Oracle8i, set the instance parameter undo_management to MANUAL.

  2. To distinguish between the two types of segments, ROLLBACK segments are called UNDO segments when AUM is used.

  3. The Oracle RDBMS will automatically create the required amount of UNDO segments in the UNDO tablespace. The DBA is no longer required to manually create the individual UNDO (rollback) segments. Oracle will create n UNDO segments (n is based on the SESSIONS instance parameter value). The UNDO segments will be named _SYSSMUn$. The UNDO segments will be owned by PUBLIC.

  4. In the same manner, the Oracle RDBMS will drop all UNDO segments, when the DBA drops an UNDO tablespace.

  5. Not all UNDO segments of an active UNDO tablespace need to be brough ONLINE by Oracle at startup. The number of UNDO segments that are brought ONLINE is determined by the SESSIONS instance parameter. Take for example, if 10 UNDO segments exist and you startup the instance with a lower SESSIONS value, the existing UNDO segments will be kept by Oracle, but onle a few of them will be brought ONLINE. All OFFLINE UNDO segments of the active UNDO tablespace will be brought ONLINE when more transactions require the use of offlined UNDO segments.

  6. It is possible to create rollback segments in an UNDO tablespace while the database is still in MANUAL mode, however, it is strongly recommended not to do it! This is actually useless since these rollback segments cannot be set ONLINE when running in AUM mode.

  7. Even though you can only use one UNDO tablespace in AUM, you can create several UNDO tablespaces. In this case, you should specify which UNDO tablespace the instance should use by setting the undo_tablespace instance parameter.

  8. UNDO tablespace are always created with locally-managed extents with system extent allocation.

Automatic Undo Management and Real Application Clusters (RAC)

It is possible to use the Automatic Undo Management features of Oracle in Real Application Clusters environments. Here are some notes about using AUM with RAC.

  1. All instances in a RAC environment must run in the same UNDO mode.

  2. You will need to set the global instance parameter undo_management to AUTO in your server parameter file. If you are using client-side parameter files, the setting for undo_management must be identical in all files.

  3. For each instance in the RAC environment, set the undo_tablespace parameter to assign the appropriate UNDO tablespace. Each instance will require is own UNDO tablespace. If the DBA does not set the undo_tablespace parameter, each instance will use the first available UNDO tablespace.

The SYSTEM Rollback Segment

As in previous versions of Oracle, the SYSTEM rollback segment is still being created in the SYSTEM tablespace and managed automatically during database creation. Even if using a designated UNDO tablespace, you cannot drop or take the SYSTEM rollback segment offline.

Depreciating the Use of Rollback Segments

According to the Oracle9i documentation, Oracle has actually depreciated the use of rollback segments for undo space management. This implies that Oracle intends to do away with rollback segments all together at some point. While this is not likely to occur for some time, it is probably a good idea to start learning about and using UNDO tablespaces.

Creating the UNDO Tablespace

A database can contain zero, one, or more UNDO tablespaces; however, AUM can only use one UNDO tablespace at a time. To enable AUM, you must perform the following tasks:

There are two different ways of creating an UNDO tablespace. The first method is by the use of the new UNDO clause of the CREATE TABLESPACE command. The second method is through the use of the CREATE DATABASE command. The next two sections look into the details of creating the UNDO tablespace using both methods.


Oracle9i introduced the new UNDO clause of the CREATE TABLESPACE as shown in the following example:
  DATAFILE '/u06/app/oradata/ORA920/undo_tbs01.dbf' SIZE 200M
In this case, we have created an UNDO tablespace called UNDO_TBS. As you can see, the CREATE UNDO TABLESPACE command syntax is much like the CREATE TABLESPACE command, including the datafile and size clauses. Note that when creating an UNDO tablespace, you can use only the datafile clause and a restricted form of the extent_management clause of the create tablespace command. Thus, you cannot define any DEFAULT STORAGE characteristics for an UNDO tablespace. Also note that Oracle creates an UNDO tablespace as a LOCALLY MANAGED tablespace, and that there is no option to create it as a DICTIONARY-MANAGED tablespace.

Once an UNDO tablespace is created, it will be brought online, along with the undo segments within it each time the database is started. This can be seen in the messages that will appear in the alert.log each time that you start the database.

Creating an UNDO Tablespace with the CREATE DATABASE Command

You can opt to create an UNDO tablespace when you initially create a database. Oracle has modified the CREATE DATABASE command to support the definition of UNDO tablespaces during the database creation process through the use of the undo tablespace clause, as shown in the following example:

      MAXLOGFILES             32
      MAXLOGMEMBERS           5
      MAXDATAFILES            600
      MAXINSTANCES            10
      MAXLOGHISTORY           1000
    '/u08/app/oradata/ORA920/system01.dbf' size 500M
      TEMPFILE '/u07/app/oradata/ORA920/temp01.dbf' SIZE 500M REUSE
    '/u06/app/oradata/ORA920/undotbs01.dbf' SIZE 300M
    GROUP 1
    '/u05/app/oradata/ORA920/redo_g01c.log') SIZE 15M,
    GROUP 2
  ( '/u03/app/oradata/ORA920/redo_g02a.log',
    '/u05/app/oradata/ORA920/redo_g02c.log') SIZE 15M,
    GROUP 3
  ( '/u03/app/oradata/ORA920/redo_g03a.log',
    '/u05/app/oradata/ORA920/redo_g03c.log') SIZE 15M

Note that we have used the UNDO TABLESPACE clause of the CREATE DATABASE command to create an UNDO tablespace called UNDOTBS. Further, we used the DATAFILE clause to define the name and location of the datafile associated with the UNDO tablespace, and we also included the SIZE and AUTOEXTEND clauses.

When issuing a CREATE DATABASE command, there are several rules that you should consider that relate to UNDO tablespaces. The rules will differ depending on how the database is configured (see the section, "Configuring the Instance for Automatic UNDO Management"). If the database instance is not configured for Automatic UNDO Management, and you omit the UNDO TABLESPACE clause, then the CREATE DATABASE statement will work as it always has, with no UNDO tablespace being created. If the instance is configured for Automatic UNDO Management, however, then the default behavior of the CREATE DATABASE statement changes.

NOTE: If you do not include the UNDO TABLESPACE clause, then Oracle will create an UNDO tablespace for you by default. This tablespace will be called SYS_UNDOTBS. This tablespace will be created using a default size of 100M for the database datafile.

Dropping an UNDO Tablespace

You can drop an UNDO tablespace in the same manner as you would drop a normal tablespace, as shown in the following example:
However, you cannot drop an UNDO tablespace if it is currently being used by an instance. If that UNDO tablespace is the active UNDO tablespace, then Oracle will generate an error.

You should exercise caution when dropping an UNDO tablespace because the DROP TABLESPACE in the case of UNDO tablespaces performs the same function as the DROP TABLESPACE ... INCLUDING CONTENTS. Because all of the contents of the UNDO tablespaces are lost, there is a chance that you may lose the data required for read-consistent images for long queries that are running. Queries that require this lost undo information will return an ORA-1555 error message. There is no risk of data loss for committed transactions; this only presents a risk for a loss of before-images that may be needed by long-running queries.

Configuring the Instance for Automatic UNDO Management

To take advantage of Oracle9i Automatic UNDO Management features, you must configure the database. Configuration of the database for Automatic UNDO Management is done by making changes to the databases parameter file (init.ora). The parameters (listed in the following table) have been added to Oracle9i to support Automatic UNDO Management.

The following will take a look at a couple of notable aspects of these parameters.

Parameter Name Default Value Valid Values Dynamic? Description
undo_management MANUAL AUTO, MANUAL No Determines whether Automatic UNDO Management is enabled in the database. AUTO enables Automatic UNDO Management and MANUAL disables the feature.
undo_retention 900 Seconds 0 to the maximum value allowed by 32 bits Immediate for system This parameter defines the minimum amount of time that Oracle will retain UNDO after it has been generated and after the generating transaction has been completed. This parameter can be modified dynamically using the alter system command. Note that Oracle will make a best effort to retain UNDO for the requested amount of time, but there is no guarantee.
undo_suppress_errors TRUE TRUE, FALSE Immediate for system; session allowed Allows you to control the displaying of error messages that result from certain SQL commands when the database is in Automatic UNDO Management mode. Be careful when setting this parameter to TRUE as it suppresses any error messages issued when attempting manual operations while in AUTO mode.
undo_tablespace You can use the undo_tablespace parameter to specify the name of the UNDO tablespace for the instance to use. If this parameter is not specified, then Oracle will choose the first available UNDO tablespace, SYS_UNDOTBS, or the SYSTEM rollback segment if no UNDO tablespace is available. Valid UNDO tablespace name. Multiple UNDO tablespaces are not supported, though Oracle does not generate an error. Immediate for system Defines one or more UNDO tablespaces that should be used by Oracle for Automatic UNDO Management. If this parameter is set, and undo_management is set to AUTO when issuing a create database command, then you must include all tablespaces listed in this parameter in the create database statement or the statement will fail. You can list multiple tablespaces here but only the last one listed will be used since Oracle allows only one.

Using the undo_tablespace Parameter

When the database is configured to use Automatic Undo Management (AUM), you have to create at least one UNDO tablespace to store the undo segments automatically created. Even though AUM uses only one UNDO tablespace at the instance level, you can create several UNDO tablespaces in the database. If this is the case, you can use the undo_tablespace parameter to control which UNDO tablespace the Oracle instance will use as in the following:
If the undo_tablespace parameter is not specified, then Oracle will choose the first available UNDO tablespace, SYS_UNDOTBS, or the SYSTEM rollback segment if no UNDO tablespace is available.

To determine which tablespace Oracle is using for AUM, you can use the following query:

SQL> SELECT  name, value
  2  FROM    v$parameter
  3  WHERE NAME IN ('undo_management','undo_tablespace');

NAME               VALUE
------------------ -----------
undo_management    AUTO
undo_tablespace    UNDOTBS

NOTE: Having several UNDO tablespaces available in the database provides the possibility to switch and use a different tablespace with smaller or larger global size for different purposes of usage, such as OLTP, BATCH.

NOTE: The undo_tablespace parameter is dynamic. This implies that you can have multiple UNDO tablespaces, however, you can have only one active UNDO tablespace in use at any given time.

Using the undo_suppress_errors Parameter

Some SQL commands such as SET TRANSACTION USE ROLLBACK SEGMENT will, by default, return an ORA-30019 error to the session issuing the SQL statement. This is because these commands are not compatible with Automatic UNDO Management. Because such an error might cause problems with existing scripts, you can set the undo_suppress_errors parameter to avoid getting the ORA-30019 error message.

NOTE: Be careful with setting this parameter to TRUE as it suppresses any error message issued when attempting manual operations while in AUTO mode.
SQL> alter rollback segment "_SYSSMU1$" online;

Rollback segment altered. 

SQL> alter rollback segment "_SYSSMU12$" offline;

Rollback segment altered. 

SQL> alter rollback segment rbs1 online;

Rollback segment altered.
All these statements seem to have executed the operation, but in reality did not do anything.

UNDO Retention Period

Oracle9i introduced the undo_retention initialization parameter to specify how long (in seconds) undo data must be retained in the UNDO tablespace before it is overwritten. This parameter enables you to minimize the chance that long running queries encounter the Snapshot to old error.

By default, undo_retention is set to 900 seconds. This means that Oracle will try not to reuse generated UNDO space for 900 seconds after the transaction committing it has been committed.

The keyword here is try, because if Oracle runs out of available UNDO space, it will begin to use space that was otherwise protected by the undo_retention parameter. The undo_retention parameter can be modified dynamically with the ALTER SYSTEM command. This comes in handy if you are finding that long-running transactions are getting "snapshot to old" errors from Oracle, though you might also need to add space to the UNDO tablespace. The undo_retention parameter also has some significant impact on another new Oracle9i feature, namely Flashback Queries.

You can estimate the amount of undo space that is required with the following formula:

  Undospace = (Undo Retention Period * Undo Blocks Per Second) + Overhead
The Overhead should account for metadata such as bitmaps.

The Data Dictionary and Automatic UNDO Management

New data dictionary views, V$UNDOSTAT and DBA_UNDO_EXTENTS, have been created that are associated with Automatic UNDO Management. In addition, the V$ROLLSTAT and V$ROLLNAME views can be used to monitor overall performance of UNDO tablespaces. Just as with Oracle8i, though, there is really little you can do to tune UNDO tablespaces.


The V$UNDOSTAT data-dictionary view provides system-generated statistics, collected every 10 minutes for the last 24 hours. This view can be used to monitor and tune UNDO space. Use this view to determine whether you have allocated sufficient space to the UNDO tablespaces for the current workload. In particular, the UNDOBLKS column is useful in determining if the tablespace is large enough. This column indicates the total number of undo blocks that were used during the statistics collection period. Thus, if the number of undo blocks consumed during the collection period is significantly larger than the size of the UNDO tablespace, you might well consider increasing the size of the UNDO tablespace for performance reasons.

Also watch the UNXPSTEALCNT column, as high numbers in this column indicate that unexpired blocks (as determined by the undo_retention parameter) are being expired prematurely and the space is being taken for use by transactions because available UNDO space was not available for those transactions. This is particularly important for new databases as more and more people begin to use the system, generating more undo, which can lead to "Snapshot to old" error messages.

Finally, the SSOLDERRCNT and NOSPACEERRCNT columns keep track of the number of Oracle errors generated during the snapshot. If these columns are non-zero, consider increasing the size of your UNDO tablespace.


This view provides information on each extent in the UNDO tablespaces, including the commit time for each transaction. It is also with this view that you can determine which tablespaces are defined as UNDO tablespaces.


Used to display the rollback segments created by Oracle. This is a classic view (not introduced in Oracle9i) and can be used just like in previous versions.
  SQL> SELECT segment_name, tablespace_name, status FROM dba_rollback_segs;

  SEGMENT_NAME                   TABLESPACE_NAME                STATUS
  ------------------------------ ------------------------------ ----------------
  SYSTEM                         SYSTEM                         ONLINE
  _SYSSMU1$                      UNDOTBS                        ONLINE
  _SYSSMU2$                      UNDOTBS                        ONLINE
  _SYSSMU3$                      UNDOTBS                        ONLINE
  _SYSSMU4$                      UNDOTBS                        ONLINE
  _SYSSMU5$                      UNDOTBS                        ONLINE
  _SYSSMU6$                      UNDOTBS                        ONLINE
  _SYSSMU7$                      UNDOTBS                        ONLINE
  _SYSSMU8$                      UNDOTBS                        ONLINE
  _SYSSMU9$                      UNDOTBS                        ONLINE
  _SYSSMU10$                     UNDOTBS                        ONLINE

  11 rows selected.


You can obtain additional information about these rollback segments by querying DBA_SEGMENTS, as shown in the following query:
  SQL> SELECT segment_name, tablespace_name, bytes, blocks, extents
    2  FROM dba_segments WHERE segment_type in ('ROLLBACK', 'TYPE2 UNDO');

  --------------- --------- ---------- ------ -------
  SYSTEM          SYSTEM       393,216     48       6
  _SYSSMU1$       UNDOTBS    1,171,456    143       3
  _SYSSMU2$       UNDOTBS    1,171,456    143       3
  _SYSSMU3$       UNDOTBS    1,171,456    143       3
  _SYSSMU4$       UNDOTBS    1,171,456    143       3
  _SYSSMU5$       UNDOTBS    1,171,456    143       3
  _SYSSMU6$       UNDOTBS    1,171,456    143       3
  _SYSSMU7$       UNDOTBS    1,171,456    143       3
  _SYSSMU8$       UNDOTBS    1,171,456    143       3
  _SYSSMU9$       UNDOTBS    1,171,456    143       3
  _SYSSMU10$      UNDOTBS    1,171,456    143       3

  11 rows selected.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 03-Jan-2004 00:00:00 EST
Page Count: 27347