Archive for July, 2011

Automatic Undo Management

July 2, 2011 Leave a comment

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. With automatic undo management, the database manages undo segments in an undo tablespace. Beginning with Release 11g, automatic undo management is the default mode for a newly installed database. An auto-extending undo tablespace named UNDOTBS1 is automatically created when you create the database with Database Configuration Assistant (DBCA).

There are three parameters associated with automatic undo management

  • UNDO_MANAGEMENT (default manual) – This is the only mandatory parameter and can be set to either auto or manual.
  • UNDO_TABLESPACE (default undo tablespace) – This specifies the tablespace to be used, of course the tablespace needs to be a undo tablespace. If you do not set this value oracle will automatically pick the one available. If no undo tablespace exists then oracle will use the system tablespace which is not a good idea (always create one).
  • UNDO_RETENTION (seconds) – Once a transaction commits the undo data for that transaction stays in the undo tablespace until space is required in which case it will be over written.

References / Source: