10 Oracle UNDO Tablespace Management Examples

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:

SQL> select tablespace_name, contents from dba_tablespaces where contents = 'UNDO'

TABLESPACE_NAME   CONTENTS
----------------- ---------
UNDOTBS           UNDO
UNDOTBS1          UNDO

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.

SQL> show parameter undo_tablespace

NAME              TYPE   VALUE
----------------- --------------
undo_tablespace   string UNDOTBS

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.

SQL> select substr(file_name,1,60) from dba_data_files where tablespace_name = 'UNDOTBS' order by 1;

SUBSTR(FILE_NAME,1,60)
--------------------------------
/u01/oradata/devdb/undotbs_01.dbf
/u01/oradata/devdb/undotbs_02.dbf
/u01/oradata/devdb/undotbs_03.dbf

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.

 select
  a.tablespace_name,
  sum(a.bytes)/(1024*1024) total_space_MB,
  round(b.free,2) Free_space_MB,
  round(b.free/(sum(a.bytes)/(1024*1024))* 100,2) percent_free
 from dba_data_files a,
  (select tablespace_name,sum(bytes)/(1024*1024) free  from dba_free_space
  group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name(+)
  group by a.tablespace_name,b.free

Output:

TABLESPACE_NAME  TOTAL_SPACE_MB FREE_SPACE_MB PERCENT_FREE
---------------- -------------- ------------- ------------
UNDOTBS                    2047       1809.44        88.39

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.

CREATE UNDO TABLESPACE UNDOTBS1
  DATAFILE '/u01/oradata/devdb/undotbs1_01.dbf' 
  SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;

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

SQL> show parameter undo_tablespace;

NAME             TYPE        VALUE
---------------- ----------- --------
undo_tablespace  string      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):

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1;

Verify that the UNDO tablespace is set properly.

SQL> show parameter undo_tablespace;
NAME             TYPE        VALUE
---------------- ----------- --------
undo_tablespace  string      UNDOTBS

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.

select file_name from dba_data_files where tablespace_name = 'UNDOTBS';

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

ALTER TABLESPACE UNDOTBS DROP DATAFILE '/u01/oradata/devdb/undotbs_02.dbf';

ALTER TABLESPACE UNDOTBS DROP DATAFILE '/u01/oradata/devdb/undotbs_03.dbf';

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.

DROP TABLESPACE UNDOTBS;

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

rm /u01/oradata/devdb/undotbs_01.dbf

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.

select count(segment_name),sum(bytes/1024/1024),status from dba_undo_extents group by status;

Output:

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS
------------------- -------------------- ---------
                 18                1.125 UNEXPIRED
                  4                  .25 EXPIRED
                293            2045.5625 ACTIVE

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:

COUNT(SEGMENT_NAME) SUM(BYTES/1024/1024) STATUS
------------------- -------------------- ---------
                478            2046.6875 UNEXPIRED
                  4                  .25 EXPIRED

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.

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

set linesize 100;

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
  TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
  UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
FROM v$UNDOSTAT WHERE rownum <= 100;

The following is a sample output:

BEGIN_TIME          END_TIME               UNDOTSN   UNDOBLKS   TXNCOUNT     MAXCON
------------------- ------------------- ---------- ---------- ---------- ----------
04/16/2016 07:41:47 04/17/2016 07:51:47         59       8762      29185          6
04/16/2016 07:31:47 04/17/2016 07:41:47         59       9110      32103          8
04/16/2016 07:21:47 04/17/2016 07:31:47         59      11850      62273          7
04/16/2016 07:11:47 04/17/2016 07:21:47         59       8801      39262          7
04/16/2016 07:01:47 04/17/2016 07:11:47         59       7711      28932          8
04/16/2016 06:51:47 04/17/2016 07:01:47         59       9872      45748          8
04/16/2016 06:41:47 04/17/2016 06:51:47         59       7898      26967          7
04/16/2016 06:31:47 04/17/2016 06:41:47         59       8811      30417          6

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.

SQL> show parameter undo_management;

NAME              TYPE        VALUE
----------------- ----------- ------
undo_management   string      AUTO

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.

SQL> show parameter undo_retention;

NAME             TYPE        VALUE
---------------- ----------- ------
undo_retention   integer     1800

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.

ALTER SYSTEM SET UNDO_RETENTION = 1800

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.

ALTER TABLESPACE UNDOTBS1
  ADD DATAFILE '/u01/oradata/devdb/undotbs1_02.dbf' 
  SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M;

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.

select
 a.tablespace_name,
 sum(a.bytes)/(1024*1024) total_space_MB,
 round(b.free,2) Free_space_MB,
 round(b.free/(sum(a.bytes)/(1024*1024))* 100,2) percent_free
 from dba_data_files a,
 (select tablespace_name,sum(bytes)/(1024*1024) free  from dba_free_space
 group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
  group by a.tablespace_name,b.free

10. UNDO System Tables

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

• 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