Home » Articles » 9i » Here. Automatic Undo Management. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was purged. V$ROLLSTATcontains rollback segment statistics. Scripting on this page enhances content navigation, but does not change the content in any way.

If you have Oracle database running on your environment, even if you are a developer or linux sysadmin, you might end-up doing some basic DBA tasks on development instance.

One of the essential DBA task is to manage UNDO on your Oracle database.

Oracle will temporarily store the data manipulation transactions in the UNDO area until those transactions are permanently committed.

This tutorial will explain everything that you need to know to manage UNDO on your Oracle database.
In Oracle DB, UNDO are used for various purpose including the following:

  • Rollback a particular transaction
  • Recover database from crash
  • Provide read consistency
  • For Flashback feature in Oracle DB
  • etc.

1. View all UNDO Tablespace


To view all UNDO tablespaces that are on your system, execute the following command:

In the above example, we have two UNDO tablespace listed. But only one of them can be active and used by the system. The other one is currently not used.

So, the best way to view the current valid UNDO tablespace is by using “show parameter” as shown below.

From the above output, look for undo_tablespace. In this example, the current undo tablespace used by the oracle database is UNDOTBS.

Execute the following command to view all the datafiles that are currently used by the UNDOTBS tablespace.

As we see from this output, this tablespace has only one datafile for the undo tablespace.

2. View UNDO Total Space and Available Free Space

The following command will give you the total space used, and the free space still available in the undo tablespace.


The above command will display all the tablespace available in your system. Just look for your undo tablespace from the list. The value displayed are in MB. The last column PERCENT_FREE displays the total percentage of free space available currently in UNDO tablespace.

3. Create New UNDO Tablespace

Creating an UNDO tablespace is similar to creating a regular tablespace.

The following example creates a new UNDO tablespace called UNDOTBS1.

Roll stats 5e roll20

In the above example:

  • /u01/oradata/devdb/undotbs1_01.dbf is the datafile that will be created and used by the UNDOTBS1 tablespace
  • The datafile will be created with initial SIZE of 1024M (1GB)
  • Autoextend is set to ON. This means that when the tablespace size goes beyond the initial size of 1024M, it will increase the size of the datafile automatically.
  • NEXT 100M indicates that the datafile will be extended in 100MB increments.
  • MAXSIZE 2047M indicates that the maximum size the datafile can grow will be 2048MB (2GB)

4. Swap UNDO Tablespace

After we created the above new tablespace for UNDO, we need to set this as the active UNDO tablespace.

As we see from this output, the current UNDO tablespace is UNDOTBS

To swap the UNDO tablespace, use the alter system set undo_tablespace as shown below.

This will set the current UNDO tablespace as UNDOTBS1 (The new tablespace that we created above):

Verify that the UNDO tablespace is set properly.

5. Drop Unused UNDO Tablespace

In the above example, since we made UNDOTBS1 as new UNDO tablespace, we can go-ahead and delete the old UNDOTBS tablespace.

Deleting an unused oracle UNDO tablespace is similar to dropping any other tablespace. If you have multiple datafiles associated with an UNDO tablespace, delete those first.

First, get a list of all the datafile for your UNDO tablespace.

Next, drop those data files one by one using alter tablespace drop datafile command as shown below.

Please note that you cannot drop the 1st datafile that was used while creating the tablespace. In this example, you can’t use drop datafile for undotbs_01.dbf

Now, drop the UNDO tablespace.


Finally, delete the 1st datafile that was associated with the UNDOTBS manually from the filesystem.

6. Display UNDO Tablespace Activities and Statistics

When your database is performing some heavy activities that require UNDO, you can see the status of the current UNDO activities from the dba_undo_extents table as shown below.

The following command will display the current UNDO activities in the system.


In the above:

  • There are lot of ACTIVE undo blocks indicating that there are some heavy UNDO activities currently happening.
  • Also, there is not much of UNEXPIRED or EXPIRED UNDO blocks.

On this particular system, after the heavy UNDO activity in the system has reduced, you’ll see something similar to the following:

In the above:

  • There is no ACTIVE undo blocks anymore. So, no UNDO activities currently happening.
  • Also, we see a heavy UNEXPIRED blocks which are from the previous UNDO activities.
  • These UNEXPIRED undo blocks will automatically become EXPIRED based on the undo retention that you’ve set on your system.

Note: If you don’t have AUTOEXTEND on for your UNDO tablespace, you’ll notice that the UNEXPIRED blocks don’t expire based on the retention policy. This is because Oracle will use a different internal method to decide when to expire those blocks. So, as a best practice, it is always recommended that you set AUTOEXTEND to ON for your UNDO tablespace.

Roll Stats 4d6

Also, you can use v$UNDOSTAT system table to view the details of the UNDO activities that happened in the past.

The following is a sample output:

In the above:

  • BEGIN_TIME is the beginning time of the UNDO transaction for this particular entry
  • END_TIME is the end time of the UNDO transaction for this particular entry
  • In this example, the duration of each entry is 10 minutes
  • UNDOTSN – Number of undo transaction happened during that time period
  • UNDOBLKS – Number of undo blocks that were active/used during that time period
  • TXNCOUNT – Total number of transactions
  • MAXCON – Maximum number of connections to the database that used this many number of UNDOs

7. Migrate from Rollback to Automatic UNDO

The following will display what type of undo management you are using on your database.

It is strongly recommended that you use Automatic UNDO management as shown above.

If you are still using ROLLBACK segments to manage your UNDO, you should consider migrating it to Automatic UNDO management.

You can use this undo advisory package to which will give you some estimate on how much UNDO space you need to use when you are converting from Rollback to UNDO: DBMS_UNDO_ADV.RBU_MIGRATION

8. Change UNDO Retention Policy

By default UNDO retention is set to 900 seconds. This means that the database will retain UNDO blocks for at least 900 seconds.

If you want to change the undo retention values, you can change it using following alter system command.

In this example, we are changing the undo retention from the default 900 seconds to 1800 seconds.

You have to be careful in setting this value. Don’t set it too high if you don’t have enough space in your UNDO tablespace to handle that much amount of UNDO generated by your system activities.

If a running transaction in your database required undo space and if the undo tablespace doesn’t have enough space, then database will start reusing the UNEXPIRED undo blocks. This means that you might get the “snapshot too old” error messages from some of your SQL statements.

9. Add Space to UNDO Tablespace

If your system generated lot of UNDO activities, and if you need to add more space to handle those (and to avoid the snapshot too old message), you can add more space to your UNDO tablespace.

The following example will add a 2nd datafile to the UNDOTBS1 with initial size of 1024MB and increases as required in the increment of 100MB. The maximum this particular datafile can grow is 2048MB.

Note: You can also set “MAXSIZE UNLIMITED”, which means that this particular undo datafile will keep growing in size as and when needed, and there is no upper limit to how big this file can grow. I tried to avoid setting UNLIMIED as much as possible, as I don’t want this file size to grow crazy when a rouge SQL statements gets executed by mistake by a developer which required lot of UNDO space.

Now, execute the following command to see the total space (and the free space available) for the UNDOTBS1 tablespace.

10. UNDO System Tables

Roll Stats Dnd

The following are some of UNDO related system tables that will give you lot of useful information about UNDO tablespace:

Roll statistics
  • V$UNDOSTAT As we saw from one of the previous example, this table contains several useful past statistics about your UNDO activities which can help you calculate the amount of UNDO space required by your system.
  • V$ROLLSTAT This table contains activities of the UNDO segments from the UNDO tablespace.
  • V$TRANSACTION For additional UNDO segment details
  • DBA_UNDO_EXTENTS This provides details of each extend in your UNDO tablespace along with their current status.
  • WRH$_UNDOSTAT Contains statistical snapshot of V$UNDOSTAT table information
  • WRH$_ROLLSTAT Contains statistical snapshot of V$ROLLSTAT table information
> Add your comment

If you enjoyed this article, you might also like..

Next post: 15 Essential Amazon AWS EC2 CLI Command Examples

Previous post: Google Chromebook Keyboard Shortcuts Reference Guide (Download Cheatsheet in PDF Format)

Roll Stats 5e

FieldData ElementData Typelength (Decimals)Check tableDescriptionConversion Routine
FLDSTATAS4LOCALCHAR1Activation Status of a Repository Object
ROLLNAMEROLLNAMECHAR30Data element (semantic domain)
ROLLSTATAS4LOCALCHAR1Activation Status of a Repository Object
DOMSTATAS4LOCALCHAR1Activation Status of a Repository Object
TEXTSTATAS4LOCALCHAR1Activation Status of a Repository Object
POSITIONTABFDPOSNUMC4Position of the field in the table
KEYFLAGKEYFLAGCHAR1Identifies a key field of a table
MANDATORYMANDATORYCHAR1Flag: Field is required (not blank)
CHECKTABLECHECKTABLECHAR30Check table name of the foreign key
ADMINFIELDADMINFIELDCHAR1Nesting depth for includes
INTLENINTLENNUMC6Internal Length in Bytes
REFTABLEREFTABLECHAR30Table for reference field
PRECFIELDPRECFIELDCHAR30Name of included table
REFFIELDREFFIELDCHAR30Reference field for currency and qty fields
CONROUTCONROUTCHAR10Check or generating module for fields
ROUTPUTLENDDLENGNUMC6Length (No. of Characters)
LOGFLAGLOGFLAGCHAR1Indicator for writing change documents
HEADLENHEADLENNUMC2Maximum length of heading
SCRLEN1SCRLEN_SNUMC2Max. length for short field label
SCRLEN2SCRLEN_MNUMC2Max. length for medium field label
SCRLEN3SCRLEN_LNUMC2Max. length for long field label
DTELGLOBALGLOBALFLAGCHAR1Flag for private DD objects (not used)
DTELMASTERMASTERLANGLANG1Original Language in Repository objectsISOLA

RESERVEDTERESERVEDTECHAR4SDIC: Reserve for data elements (not used)
LENGDDLENGNUMC6Length (No. of Characters)
LOWERCASELOWERCASECHAR1Lowercase letters allowed/not allowed
SIGNFLAGSIGNFLAGCHAR1Flag for sign in numerical fields
LANGFLAGLANGFLAGCHAR1Flag for language-dependent values(not used)
VALEXIVALEXICHAR1Existence of fixed values
MASKAS4MASKCHAR20Template (not used)
MASKLENMASKLENNUMC4Template length (not used)
DOMMASTERMASTERLANGLANG1Original Language in Repository objectsISOLA
RESERVEDOMRESERVEDOMCHAR4Reserve for domains (not used)
DOMGLOBALGLOBALFLAGCHAR1Flag for private DD objects (not used)
DDTEXTAS4TEXTCHAR60Short Description of Repository Objects


Roll Stats Avrae

Key field
Non-key field