Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Managing the UNDO Tablespace

Oracle Tips by Burleson

There are several initialization parameters that control the UNDO tablespace feature of Oracle. They are:

UNDO_MANAGEMENT. Can be set to AUTO or MANUAL. In AUTO, the database manages undo operations; under MANUAL, the DBA manages undo operations. The default value is MANUAL, and any UNDO_TABLESPACE specification is ignored. If set to MANUAL, management of rollback segments is identical to pre-9i rollback segment management.

UNDO_TABLESPACE. A dynamic parameter that specifies which tablespace to use as an UNDO tablespace.

UNDO_RETENTION. A dynamic parameter that specifies the length of time to retain undo information for use in flashback queries; the default is 900 seconds.

UNDO_SUPPRESS_ERRORS. Either TRUE or FALSE. Used to suppress error messages if manual undo management SQL commands are entered in auto undo management mode.

Creation of the UNDO Tablespace

As stated above, the UNDO tablespace can be created either when the database is created, using the UNDO TABLESPACE clause of the CREATE DATABASE command, or it an be added later, using the CREATE UNDO TABLESPACE command and the appropriate initialization parameters set for it. An example UNDO tablespace creation command is shown here:

CREATE UNDO TABLESPACE undo_tbs1 DATAFILE '/oracle3/oradata/galinux1/data/undo_tbs2.dbf' SIZE 200M AUTOEXTEND ON;

Oracle determines the other attributes of the undo tablespace automatically; you only need to specify the datafile clause and size information. The Oracle system will automatically assign an UNDO tablespace with locally managed, auto-allocated extents; the maximum size will be set at approximately one and a half times the specified size. You can specify the autoextend parameters and the extent management parameters in the CREATE UNDO command, but options such as LOGGING will cause an error.

Altering an UNDO Tablespace

An UNDO tablespace can only be altered for the following items:

* Adding a datafile.

* Renaming a datafile.

* Bringing a datafile on- or offline.

* Beginning or ending backup.

* Resizing a datafile.

Dropping an UNDO Tablespace

The UNDO tablespace(s) are dropped using the DROP TABLESPACE command. The UNDO tablespace to be dropped cannot have any currently active transactions.

Switching Active UNDO Tablespaces

Before dropping an UNDO tablespace, you will need to make sure there is a second UNDO tablespace; then you must switch the database to use the new tablespace. The ALTER SYSTEM command is used to switch the database from one UNDO tablespace to another, as shown here:


Of course, if for any reason, the switch cannot be accomplished, an error is issued and no switch occurs. Errors that will prevent a switch are:

* Tablespace to be switched to doesn't exist.

* Tablespace to be switched to isn't an UNDO tablespace.

* Tablespace to be switched to is in use by another RAC instance.

If there are active transactions in the old tablespace, the tablespace goes into PENDING OFFLINE status until the transactions complete. A PENDING OFFLINE tablespace is in a state of LIMBO until its incomplete transactions are finished. A tablespace in PENDING OFFLINE cannot be dropped or used. If the UNDO_TABLESPACE parameter is set to the null string, which in Oracle is represented by two single quotes with no space between them. (for example, ALTER SYSTEM SET UNDO_TABLESPACE='') then the existing undo tablespace is taken offline and no new undo tablespace is assigned. @@@Au: Close double quote mark correct here? No open quote given. Yes these are two single quotes with no space, in Oracle this means the null string.@@@ The assignment of the UNDO tablespace to the null string would be used if you were switching to MANUAL managed undo.

Assigning Usage Quotas for UNDO Tablespaces

A new feature in Oracle is the ability to establish quotas on UNDO space for specific types of users. This is accomplished using the DBMS_RESOURCE_MANAGER package. If a resource group is assigned a quota on an UNDO tablespace, and that quota is reached, the current transaction that is generating UNDO is halted and rolled back and no further UNDO transactions will be allowed from that resource group until the usage of UNDO space drops below its quota. If no quota is assigned, a resource group is considered by default to be unlimited. I will cover the use of DBMS_RESOURCE_MANAGER in detail in Chapter 9, User and Security Administration.

Retention Periods and UNDO Tablespaces

One of the interesting new features in Oracle dealing with UNDO tablespaces is the ability to perform flashback queries. Flashback queries allow you to view data that has been updated or deleted as it was before it was updated or deleted. Being able to look back in time is accomplished through the use of the UNDO tablespace retention capability. The DBA can specify how much data, based on an asset retention period, is maintained for flashback query. The default is 900 seconds (15 minutes). The retention period for UNDO tablespaces is set using the UNDO_RETENTION initialization parameter. The information retained for flashback query is persistent between database startups. This persistence of data means that even after a crash and restart the data will still be available. The retention period restarts its countdown after each startup.

The ALTER SYSTEM command can also be used to reset the retention period dynamically. The effect of the ALTER SYSTEM command on retention time is immediate but will only be honored completely if there is enough room in the UNDO tablespace to accommodate the data requirements.

Flashback queries are facilitated using the DBMS_FLASHBACK package, which is covered in Chapter 9. The space required to facilitate flashback is calculated by the formula:

        UndoSpace = UR * UPS + overhead


* UndoSpace is the number of undo blocks required.

* UR is the value of UNDO_RETENTION in seconds.

* UPS is the number of UNDO blocks generated per second.

* Overhead is a small number of blocks used for metadata..

The information for the above calculation is contained in the v$undostat view. A sample SELECT command to get this information about number of transaction UNDO blocks per second since database startup would be:

SELECT SUM(undoblks)/((MAX(end_time)-MIN(BEGIN_TIME))*24*3600)
FROM v$undostat;

The problem with this SELECT is that it will give an abnormally low value for databases with periods of low activity. It may be better to look at all of the available periods and get values that correspond to high activity times:

SELECT undoblks/((end_time-begin_time)*24*3600)) FROM v$undostat;

More useful fields from the V$UNDOSTAT view are SSOLDERRCNT and NOSPACEERRCNT. If you get values in either of these columns for your current UNDO tablespace, then you may need either a smaller UNDO_RETENTION parameter value or larger UNDO tablespaces.

Other useful UNDO views are:

V$ROLLSTAT. Shows behavior of undo or rollback segments.

V$TRANSACTION. Contains undo segment usage information.

DBA_UNDO_EXTENTS. Shows the commit time for each extent in the UNDO tablespace.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter