Automatic UNDO in Oracle

A recent question in an Oracle forum regarding Automatic UNDO Management shows that the topic is still misunderstood by some. The basic idea behind Automatic UNDO Management is that the database essentially does the work for the DBA, freeing him or her from a tedious task of monitoring and adjusting UNDO (or rollback) segments. This mechanism also manages the number of segments created at database startup, which initiated the question and fueled the discussion. How does Oracle decide how many UNDO segments to create at startup, and what is the underlying goal of the process? Let’s investigate.

Automatic UNDO Management attempts to do the following:

  • Manage the UNDO tablespace
  • Allocate UNDO segments
  • Assign transactions to UNDO segments
  • Release UNDO segments when transactions complete

To do all of this Oracle requires an UNDO tablespace that is different from the others. No standard database objects can be created in an UNDO tablespace (users cannot put tables or indexes there) so Oracle can manage the objects unimpeded. This makes the job easier for the DBA, and also restricts what a DBA can do to the tablespace. The list of available tasks a DBA can perform is shown below:

  • Adding a data file
  • Renaming a data file
  • Bringing a data file online or taking it offline
  • Beginning or ending an open backup on a data file
  • Enabling and disabling undo retention guarantee

Only one UNDO tablespace can be active for a database but that doesn’t mean additional UNDO tablespaces can’t be created to address different processing scenarios. For example UNDOTBS1 could be a ‘standard’ UNDO tablespace not set to autoextend since OLTP transactions are usually small and don’t need the extra segment space, where UNDOTBS2 would have its datafiles set to autoextend, so that large batch processing jobs could complete without issue. Setting which UNDO tablespace is used is a simple task:


This parameter is dynamic so no restart of the database is necessary, thus it can be included as a command in a batch processing script; once the command is run any new transactions will use UNDOTBS2; existing transactions using UNDOTBS1 will continue to completion and when all current transactions are done UNDOTBS1 will go offline. For a RAC environment doing this may not be possible if, for some reason, more than one node is using the same UNDO tablespace or the UNDO tablespace being switched to is the same as that for another node. In the second case the initial switch would occur without error; when an attempt was made to switch back to the original UNDO tablespace for the node in question the switch would fail since the current UNDO tablespace is in use by two nodes, which would be the first case. Granted neither situation is likely to occur but it is wise to be prepared in the event someone has made such a switch.

Although it is not a recommended practice, quotas can be established for the UNDO tablespace for a resource group using the Resource Manager. Users in a resource group will only get the total UNDO specified in the UNDO_POOL directive. The UNDO_POOL setting applies to the resource group, not each individual member, so if one user of a resource group consumes all of the assigned UNDO space before an update transaction completes that transaction is terminated. Until a rollback is issued, releasing the UNDO, no other users of the group can perform updates.

Several views provide information on the UNDO tablespace and its activity:

	DBA_HIST_UNDOSTAT <-- needs="" no="" licensing="" in="" versions="" 11="" 2="" and="" later="" span="">

When Automatic UNDO management is used V$ROLLSTAT provides information on the UNDO segments. [The GV$ROLLSTAT view provides cluster-wide UNDO information.] The V$UNDOSTAT view can be very helpful in tracking UNDO usage over a short period of time, such as the last 24 to 48 hours. For longer periods DBA_HIST_UNDOSTAT can provide usage trends over the configured AWR retention window. Looking at V$UNDOSTAT:

  4      FROM v$UNDOSTAT WHERE rownum <= 144;

------------------- ------------------- ----------- ----------- ----------- -----------
09/25/2015 14:05:13 09/25/2015 14:07:56           5          16          40           4
09/25/2015 13:55:13 09/25/2015 14:05:13           5         211         459           4
09/25/2015 13:45:13 09/25/2015 13:55:13           5          67         200           4
09/25/2015 13:35:13 09/25/2015 13:45:13           5          69         193           4
09/24/2015 14:45:13 09/24/2015 14:55:13           5          19         149           2
09/24/2015 14:35:13 09/24/2015 14:45:13           5          11          97           1
09/24/2015 14:25:13 09/24/2015 14:35:13           5          14         127           2
09/24/2015 14:15:13 09/24/2015 14:25:13           5          21         143           3

144 rows selected.


This report shows the UNDO tablespace being used (UNDOTSN), the blocks consumed (UNDOBLKS), the total number of transactions executed during the period (TXNCOUNT) and the maximum number of concurrent transactions in that same period (MAXCON). This same report, for a longer window, is returned from DBA_HIST_UNDOSTAT; it would be a good idea to order the results to give a clearer picture of the usage trends over time.

UNDO retention is set with the undo_retention parameter, which provides the minimum retention period. Oracle does its best to honor that retention if the UNDO datafiles are set to autoextend. Oracle automatically manages the retention setting by setting the retention period to somewhat longer than the longest currently running active query to avoid “Snapshot too old” errors. As UNDO needs increase Oracle extends the datafiles to the limit set by MAXSIZE; when that limit is reached then unexpired UNDO information may be overwritten, oldest first. When UNDO datafiles are set to fixed sizes then this setting is ignored.

UNDO retention can be guaranteed but that can cause DML operations to fail on active systems. When the UNDO tablespace is created RETENTION GUARANTEE can be specified; it can also be set for exising UNDO tablespaces with the ALTER TABLESPACE statement. To reverse that change the ALTER TABLESPACE command can be used with RETENTION NOGUARANTEE specified.

V$UNDOSTAT allows the tuned UNDO retention size to be tracked:

SQL> select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
   2    to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
   3    from v$undostat order by end_time;

--------------- --------------- -------------------
22-SEP-15 11:45 22-SEP-15 11:55               36000
22-SEP-15 11:55 22-SEP-15 12:05               36000
22-SEP-15 12:05 22-SEP-15 12:15               36000
22-SEP-15 12:15 22-SEP-15 12:25               36000
22-SEP-15 12:25 22-SEP-15 12:35               36000
25-SEP-15 14:45 25-SEP-15 14:55               36000
25-SEP-15 14:55 25-SEP-15 14:57               36000

452 rows selected.


Changes to the retention will be shown in the query results, allowing the DBA to monitor the automatic retention setting. For some systems the undo_retention parameter setting won’t be overridden (as in the results shown above) but in other cases it may be significantly larger to accomodate longer transactions.

Oracle sets the initial number of UNDO segments based on the system activity; by default it appears that 10 is the ‘magic’ number Oracle allocates, subject to change as the system activity increases. It may not be unusual to have almost 400 UNDO segments, or more, allocated for extremely busy systems. DBA_SEGMENTS can provide that information; simply specify the UNDO tablespace for tablespace_name in your query.

Automatic UNDO Management isn’t voodoo or black magic, although it can seem that way when it isn’t clearly understood. I hope you now have a better understanding of the mechanism and how it behaves, making your job a bit easier.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles