Automatic Undo Management

In earlier versions of ORACLE, transactions undo information was stored into undo segment until a commit or rollback command was issued. When the commit or rollback command was issued, it purge the undo segment which contains corresponding transaction undo information. This system is known as manual undo management.

In version 9i ORACLE introduce automatic undo management along with manual management. So one can use either automatic or manual management but not both at a time. The new method freed DBA from periodical undo management and tuning. In also facilitate the DBA to specify how long undo information is stored after a commit occur. This feature eliminate the "snapshot too old" error of long running queries and also support ORACLE flashback queries.

Create Undo Tablespace

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u01/undotbs01_01.dbf'
SIZE 1024M BLOCKSIZE 16K;

alter system set undo_tablespace='UNDOTBS' scope=both;

Enabling Automatic Undo Management

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.

Please set the following parameters

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

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

Maintenance of Undo Tablespace

-- Add a datafile.

ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u0/undo0102.dbf'
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

-- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/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;

Sometimes the undo tablespace become too big to manage. In such case you can resize the datafiles or create a new undo tablespace in another disk location

- Resize an undo datafile.

ALTER DATABASE DATAFILE '/u0/undo0102.dbf' RESIZE 10M;

-- create new undo tablespace and drop the old one

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/u0/undotbs01_01.dbf' SIZE 1024M BLOCKSIZE 16K;

ALTER SYSTEM SET undo_tablespace='UNDOTBS' scope=both;

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Monitoring Undo Tablespace

You may use the following dictionary viewers:

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS