10 Managing the Undo Tablespace

Altering an Undo Tablespace

Undo tablespa ces are altered using the ALTER TABLESPACE statement. However, since most aspects of undo tablespaces are system managed , you need only be concerned with the following actions:

• Adding a datafile

• Renaming a datafile

• Bringing a datafile online or taking it offline

• Beginni ng or ending an open backup on a datafile

• Enabling and disabling undo retention guarantee

These are also the only attributes you are permitted to alter.

If an undo tablespace runs out of space, or you want to prev ent it from doing so, you can add more files to it or resize existing datafiles.

The following example adds another datafile t o undo tablespace undotbs_01:

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

You can use the ALTER DATABASE ... DATAFILE stateme nt to resize or extend a datafile.

< /a>

Dropping an Undo Tablespace

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tab lespace undotbs_01:

DROP TABLESPACE undotbs_01;

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a tr ansaction died but has not yet been recovered), the DROP TABLESPACE statement fails. However, since DROP TABLESPAC E drops an undo tablespace even if it contains unexpired undo information (within retention period), you must be careful not t o drop an undo tablespace if undo information is needed by some existing queries.

DROP TABLESPACE for undo tables paces behaves like DROP TABLESPACE ... INCLUDING CONTENTS. All contents of the undo tablespace are removed.

Switching Undo Tablespaces

You can switch from using one un do tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER S YSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablesp ace:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

Assuming undotbs_01 is the current undo tablespace, after this command successfully executes, the instance uses undotbs_02 in place of undot bs_01 as its undo tablespace.

If any of the following conditions exist for the tablespace being switched to, an error i s reported and no switching occurs:

• The tablespace does not exist

• The table space is not an undo tablespace

• The tablespace is already being used by another instance (in a RAC envir onment only)

The database is online while the switch operation is performed, and user transactions can be executed w hile this command is being executed. When the switch operation completes successfully, all transactions started after the switch oper ation began are assigned to transaction tables in the new undo tablespace.

The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace ente rs into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records f or new user transactions cannot be stored in this undo tablespace.

An undo tablespace can exist in this PENDING OFFLINE< /code> mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have com mitted, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).

If the paramete r value for UNDO TABLESPACE is set to '' (two single quotes), then the current undo tablespace is switched out and the n ext available undo tablespace is switched in. Use this statement with care, because if there is no undo tablespace available, the SYSTEM rollback segment is used. This causes ORA-01552 errors to be issued for any attempts to write non-SYSTEM related undo to the SYSTEM rollback segment.

The following example unassigns the current undo tablespace:

< pre xml:space="preserve">ALTER SYSTEM SET UNDO_TABLESPACE = '';

Es tablishing User Quotas for Undo Space

The Oracle Database Resource Manager ca n be used to establish user quotas for undo space. The Database Resource Manager directive UNDO_POOL allows DBAs to limi t the amount of undo space consumed by a group of users (resource consumer group).

You can specify an undo pool for each consu mer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the redo is terminated. No other mem bers of the consumer group can perform further updates until undo space is freed from the pool.

When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.