Donald K. Burleson Oracle Tips

Oracle9i UNDO log management

Starting in Oracle9i, rollback segments are re-named undo logs.

Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

In Oracle9i, manual undo management is still available in addition, but most Oracle professionals choose the new automatic method which frees DBAs from routine undo management tasks and tuning.

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing "snapshot too old" errors on long running queries.

This is done by setting the undo_retention parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle9i take care of the rest. 

Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set undo_management=auto and bounce your database:

CREATE UNDO TABLESPACE

   ts_undo
DATAFILE

   '/u01/oradata/prod/ts_undo.dbf'
SIZE 2M REUSE AUTOEXTEND ON;

Alter system set undo_tablespace=ts_undo;

Alter system set undo_management=auto;

When the instance starts up, Oracle automatically selects for use the first available undo tablespace.

Automatic undo management requires a locally managed undo tablespace to store undo segments in.

Once created, undo information can be queried using the following new Oracle9i views:

• V$UNDOSTAT

• V$ROLLSTAT

• V$TRANSACTION

• DBA_UNDO_EXTENTS

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: