Oracle UNDO

In this article I will cover a few important topics surrounding UNDO management in Oracle, specifically automatic undo, including the follwing:

  • How to monitor the amount of UNDO being used
  • UNDO_RETENTION parameter setting
  • ORA-30036 unable to extend segment in undo tablespace
  • ORA-01555 snapshot too old
  • How to change the default UNDO tablespace

Undo in Oracle – Monitor Usage

Here is a great SQL query to show you how much UNDO was in use in your database over a certain time period. Use it to monitor how much space was required for a particular script, for example. You could also modify it to insert into a table so that you have a history over time for future reference.

col "TXNs" format 999,999
col "STL_BLK" format 99,999
col "ACTIVE MB" format 99,999,999
col "UNEXP MB" format 99,999,999
col "EXP MB" format 99,999,999
col "TOTAL MB" format 99,999,999
set lines 120
set pages 1000

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

WITH tbs_blk AS
(select block_size
from dba_tablespaces
where contents='UNDO')
SELECT begin_time
, end_time
, txncount "TXNs"
, (ACTIVEBLKS * (select block_size from tbs_blk)/ 1024/1024) "ACTIVE MB"
, (UNEXPIREDBLKS * (select block_size from tbs_blk) / 1024/1024) "UNEXP MB"
, (EXPIREDBLKS * (select block_size from tbs_blk) / 1024/1024) "EXP MB"
, (undoblks * (select block_size from tbs_blk) / 1024/1024) "TOTAL MB"
FROM v$undostat
where begin_time > (sysdate-2)
ORDER BY begin_time desc;

In the statement above I have limited the data being displayed to sysdate-2. If you want to see more data change this accordingly. You can go back up to 7 days unless the database has been restarted in this time. Here is what the different columns in the output mean:

Begin_time – This is the start of a 10 minute period from v$undostat

End_time – This is the end of a 10 minute period from v$undostat

TXNs – Displays the number of transactions which occurred within the being and end time period

ACTIVE MB – Total amount of MBs of UNDO which were being used by an active transaction, i.e. one which had not issued a commit or rollback statement.

UNEXP MB – These are undo blocks which are not actively in use by a current transaction, but they are still within the undo_retention period. undo_retention is covered next on in more detail but in short it is undo that you would like to keep, if possible, for a specified amount of time as indicated by undo_retention.

EXP MB – Undo blocks which are not actively in use by a current transaction and they were last active more than the number of seconds as specified by the undo_retention parameter. These are the first blocks that Oracle will attempt to use when UNDO blocks are required for use.
This should help you to see exactly what was happening and when it was happening within your database, in terms of the UNDO usage.


I was going to write about the UNDO_RETENTION paramter here but then I realised that it would be more appropriate to go into the details when I speak about the ORA-1555 error slightly further down in this article, so keep on reading and you will get the information that you need to know a little further down.

ORA-30036: unable to extend segment by X in undo tablespace ‘UNDOTBS1’

The short answer to this is that you need to increase the size of your UNDO tablespace. However, this might not necessarily be completely true…Why? Well, if a user had a long running update statement running, for example, and it ran out of UNDO space with the ORA-30036 error message that statement would have to rollback. This means that Oracle will be re-applying all of the UNDO records back to the datafiles.

If this is happening there will be a large amount of UNDO space used which means that there will not be much free space available in the UNDO tablespace for other active transactions, especially if they are fairly chunky themselves. Bear this in mind when it happens on a shared DEV environment.

Is a Statement Rolling back?

I always take a look at the V$TRANSACTION view to see what active transactions there are in the database and I use this SQL query:

select status
, start_time
, space
, ptx
, TRUNC((used_ublk*bp.block_size)/1024/1024) Blocks_MB
, log_io
, phy_io
, cr_get
from v$transaction, (select block_size
from v$buffer_pool
where name = 'DEFAULT') bp
order by start_time;

In addition to that, you can see how much ACTIVE UNDO there is from the previous SQL statement based on the V$UNDOSTAT view. If you run this SQL repeatedly you should notice that the “Blocks_MB” number decreases as the statement is rolling back.

Rollback Faster

If you run the above command and it seems like it’s going to take an age for the transaction to rollback, and the disk I/O levels are not that high it’s probably because the transaction is rolling back in serial. One way to increase your rollback performance is to configure the parameters which enable parallel rollback and restart the database with the abort command. You could use STARTUP FORCE, which does a SHUTDOWN ABORT and STARTUP commands.

The following parameters will need to be configured to take full advantage of parallel rollback:

recovery_parallelism – how many parallel slaves to use for parallel recovery, set to 0 or 1 to force serial recovery. It cannot exceed parallel_max_servers
fast_start_parallel_rollback – whether parallel rollback is enabled and to what degree:

FALSE = Parallel rollback is disabled

LOW = Limits the maximum degree of parallelism to 2 * CPU_COUNT

HIGH = Limits the maximum degree of parallelism to 4 * CPU_COUNT

ORA-01555 Snapshot Too Old

There are many ways in which you can receive the ORA 01555 error message. Let’s look at the most common ways to clear up any misconceptions.

      This init.ora parameter is required when using automatic undo management, from 9i onwards. It’s aim is simple: Attempt to keep undo records which are not active (no longer belong to a running transaction, but one which has since committed or rolled back) for a specified amount of time, as indicated by the UNDO_RETENTION parameter.
      Specify it in seconds and set it to a value larger than the time that your longest SQL query will be running. The reason for this? Imagine you have a select SQL statement which starts at 9am and runs for 30 minutes (1800 seconds). This statement needs to query a view of the data as at 9am, so if any data it is interested in querying is modified after 9am it has to read the UNDO record to know what it was at 9am. If you set UNDO_RETENTION to anything less than 1800, the undo records could validly be overwritten by a new transaction. Then your SQL query would receive the ORA-01555 snapshot too old error.
      Setting the UNDO_RETENTION parameter is no guarantee that the committed data will remain available for that time, it is just a soft setting, telling Oracle what your preference is. If there are active transactions which need UNDO, they will take higher priority over the already committed information and you may still receive the ORA-01555 error. You could use the retention guarantee to ensure you always had the information available, which is at the cost of DML operations – they will then fail.
    1. Fetch Across Commit
      When you write a piece of pl/sql, it may look something like this:
for record in cursor loop
-- Update/insert statement here
end loop;
      This is an example of fetching across a commit because for every record in your cursor you are committing afterwards, which means the next select (which is probably looking at the same data set) will have to use UNDO to create a read consistent view of the data as it was when the cursor was first opened. To make this less likely to receive the ORA-01555 snapshot too old error message, you should change it to put the commit outside of the loop like this:
for record in cursor loop
-- Update/insert statement here
end loop;
      Or, use a counter variable to commit after X number of records, like this:
vCounter NUMBER(6);

vCounter := 0;

for record in cursor loop
-- Update/insert statement here
vCounter := vCounter +1;
If vCounter >= 10000 THEN
end if;
end loop;

Change UNDO Tablespace

If you would like to change the UNDO tablespace in your Oracle database and you are using automatic undo management then it’s quite simple to do this. First of all check that you are using automatic undo management by checking the value for the UNDO_MANAGEMENT parameter:

show parameter undo_management

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

Next, create a new UNDO tablespace, set it as the default UNDO tablespace and remove the old tablespace:

SQL> create undo tablespace UNDOTBS1 datafile 'D:\oradata\Oradata\SYSTEM\UNDOTBS01.dbf' size 500M;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS1' scope=both;

System altered.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

Check that the files have been removed from the file system and you’re all done.

NOTE: Make sure that you specify the keyword “undo” when creating the tablespace.

I hope this has helped you with understanding more about the UNDO tablespace in Oracle. If you have any questions let me know either by email or in the comments below

Like it, share it...

Category: Articles

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *