8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 9i » Here

Automatic Undo Management

Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was purged. In Oracle 9i this method of manual undo management is still available in addition to a new automatic method which frees DBAs from routine undo management tasks and tuning. In addition it allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries in addition to supporting Oracle flashback query.

It is not possible to use both methods in a single instance simultaneous, but the method can be switched, a procedure which requires the instance to be bounced.

Undo Tablespace Creation

Automatic undo management requires a locally managed undo tablespace to store undo segments in. Undo tablespaces can be created during database creation or using the CREATE UNDO TABLESPACE statement.

-- As part of database creation
CREATE DATABASE rbdb1
  CONTROLFILE REUSE
...
  UNDO TABLESPACE undotbs_01
    DATAFILE 'C:\Oracle\Ordata\TSH1\undo0101.dbf'
    SIZE 100M REUSE AUTOEXTEND ON;

-- Using the create undo tablespace statement
CREATE UNDO TABLESPACE undotbs_02
  DATAFILE 'C:\Oracle\Ordata\TSH1\undo0201.dbf'
  SIZE 100M REUSE AUTOEXTEND ON;

If undo_management is set to auto and no undo_tablespace is defined Oracle will create one during the database creation.

Enabling Automatic Undo Management

Since the default undo management mode is MANUAL, the instance must be told to use AUTO mode at instance startup. To do this the following initialization parameters can be set.

UNDO_MANAGEMENT = AUTO       # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION  = 900        # The time undo is retained.
                             # Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE  # Suppress errors when MANUAL undo admin
                             # SQL statements are issued.

Several of these parameters can be altered while the instance is up, but the UNDO_MANAGEMENT parameter is static.

-- Dynamic Parameters.
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS_02;
ALTER SYSTEM SET UNDO_RETENTION=5;
ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=FALSE;

-- Static Parameters.
ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;

Maintenance

Maintenance of undo tablespaces is similar to that of regular tablespaces, although some options are not neccessary as Oracle takes over the burden of most management tasks.

-- Add a datafile.
ALTER TABLESPACE undotbs_01
  ADD DATAFILE 'C:\Oracle\Ordata\TSH1\undo0102.dbf'
  AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

-- Resize an undo datafile.
ALTER DATABASE DATAFILE 'C:\Oracle\Ordata\TSH1\undo0102.dbf'
  RESIZE 10M;

-- Perform backup operations
ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
ALTER TABLESPACE undotbs_01 END BACKUP;

-- Drop an undo tablespace.
DROP TABLESPACE undotbs_01;

In the last example the tablespace will only be dropped if it is not currently being used or contains undo information for a current transaction. It will however drop tablespaces where the undo information has not yet expired, thus affecting long running queries and flashback query.

Monitoring

Undo information can be queried using the following views.

By default all users have unlimited access to undo tablespaces, although this can be limited using the Resource Manager directive UNDO_POOL.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.