Automatic Undo Management (AUM) ORACLE 9i

In Oracle 8i and below, Rollback Segments provide read consistency and the ability to rollback transactions. In Oracle 9i, Undo segments can be used to provide this functionality. The advantage of using Automatic Undo Management is that it relieves the DBA of manually creating, sizing and monitoring the rollback segments in the database.
To start using Automatic Undo Management one must create an UNDO-type tablespace and set some initialisation parameters.
Create an Undo Tablespace:
Oracle can only create undo segments in special UNDO-type tablespaces. One can create an undo tablespace with database creation or by creating separate UNDO tablespaces:
— Using the create undo tablespace statement
CREATE UNDO TABLESPACE undotbs
DATAFILE ‘/dev/vx/rdsk/dg_db/db_02048_raw_040’ SIZE 500M AUTOEXTEND ON MAXSIZE 10G;
The following initialisation parameters can be used to enable and control AUM (do a “show parameters undo” to see your settings):
UNDO_MANAGEMENT (MANUAL or AUTO)
– One MUST set this parameter to AUTO to enable Automatic Undo Management. The default is MANUAL for both Oracle 8i and Oracle 9i databases. One must restart the database to switch between MANUAL and AUTO mode.
UNDO_TABLESPACE (valid UNDO-type tablespace name)
– Specifies which undo tablespace to use.
UNDO_RETENTION (time in seconds)
– Specifies the amount of undo the instance should attempt to retain. The default is 9000 seconds or 15 minutes. If the UNDO tablespace is too small compared to the retention time, one can still get the famous ORA-1555 errors.
UNDO_SUPPRESS_ERRORS (TRUE or FALSE)
– Specifies whether or not to return an exception when DDL and “SET TRANSACTION USE ROLLBACK SEGMENT” statements are issued. The default is FALSE.
Example:
SQL> alter system set undo_management=auto scope=spfile;
SQL> create UNDO tablespace UNDOTS1 datafile ‘/dev/vx/rdsk/dg_db/db_02048_raw_040’ size 500M AUTOEXTEND ON MAXSIZE 10G;
SQL> alter system set undo_tablespace=UNDOTS1 scope=spfile;
SQL> shutdown immediate;
…
SQL> startup
The following views can be used to monitor Undo Segments:
The traditional views like DBA_ROLLBACK_SEGS, V$ROLLSTAT, V$ROLLNAME, and V$TRANSACTION are still available.
DBA_UNDO_EXTENTS – shows when each extent in the undo tablespace was committed.
V$UNDOSTAT – shows the undo usage for the last 24 hours. Each row records a ten minute interval defined by START_TIME and END_TIME. The key field is UNDO_BLOCKS. Note that one can size the UNDO tablespace according to this info: UNDO TS SIZE = UNDO_RETENTION * UNDO_BLOCKS * BLOCK_SIZE.