Types of Undo Data

There are three types of undo segments. The first two are similar to the two types of tablespacesSYSTEM and non-SYSTEMand the third is called deferred.

SYSTEM

SYSTEM undo segments are used for objects in the SYSTEM tablespace. The SYSTEM undo segment is created in the SYSTEM tablespace whenever a database is created and works the same regardless of whether the maintenance mode is manual or automatic.

Non-SYSTEM

Non-SYSTEM undo segments are used for all objects in any tablespace other than the SYSTEM tablespace. These segments can either be created with automatic undo management or manual undo management. The two different ways that segments can be managed are detailed as follows:

• Manual undo management In manual undo management mode, a non-SYSTEM undo segment created deliberately by the database administrator can be utilized to maintain undo information on non-SYSTEM objects located in tablespaces. Private undo segments are those brought online by an instance because they are listed in a parameter file or because a DBA has issued an ALTER ROLLBACK SEGMENT command. Public manual undo segments form a pool of undo segments that then become available to the database. They are normally used with Oracle Parallel Server to create a pool of undo segments that can be used by any of the Parallel Server instances. You must create at least one non-SYSTEM undo segment before you can create anything in the database, including additional tablespaces. This DBA-created, non-SYSTEM undo segment is created in the SYSTEM tablespace and should be taken offline and dropped after you have created your undo tablespace and segments.

• Automatic undo management In automatic undo management, undo data is managed using the undo tablespace. You, as DBA, allocate one active undo tablespace per instance, allocated with sufficient space for the workload of the instance. With this undo tablespace, Oracle automatically maintains the undo data. The undo segments are created in the database with the naming convention _SYSSMUn$ (for example, _SYSSMU1$, SYSSMU2$, and _SYSSMU3$).

Deferred

Deferred is used whenever tablespaces are taken OFFLINE IMMEDIATE, OFFLINE TEMPORARY, or OFFLINE FOR RECOVERY, and transactions are accessing the data in these tablespaces. They are used to facilitate the rolling back of transactions when the tablespace is brought back online and are dropped automatically when they are no longer needed. Because they are created and maintained by Oracle, no maintenance is required on the part of the DBA, but the knowledge that they exist and their purpose is important.

Oracle suggests that automatic undo management is the preferred method in Oracle 9i. In the next section, we discuss how to configure your instance and database to use automatic undo management.