Mastering Oracle UNDO Tablespace: Monitoring, Tuning, and Space Reclamation

1. Intervening in Automatic UNDO Management

Oracle typically manages UNDO tablespaces automatically (undo_management=AUTO). While direct intervention is limited, DBAs can influence behavior through initialization parameters such as undo_management, undo_retention, undo_tablespace, _smu_debug_mode, and _undo_autotune.

2. Automatic UNDO Retention

Since Oracle 10g, Automatic UNDO Retention is enabled by default. The database retains undo data for at least the period specified by UNDO_RETENTION, adjusting the value based on tablespace size and workload statistics. The current retention can be queried:

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
       TUNED_UNDORETENTION FROM V$UNDOSTAT;

When automatic retention causes rapid UNDO growth, consider disabling it by setting _undo_autotune=FALSE.

3. Controlling TUNED_UNDORETENTION Values

If the UNDO tablespace is not auto‑extendable, TUNED_UNDORETENTION may compute excessively large values for large tablespaces. Setting _smu_debug_mode=33554432 forces the calculation to use (MAXQUERYLEN + 300) and the maximum UNDO_RETENTION instead of tablespace usage.

4. Auto‑extend Behavior of UNDO Tablespaces

When a UNDO tablespace is auto‑extendable, Oracle may keep EXTENTs in the EXPIRED state to reduce ORA‑01555 occurrences, which can cause the tablespace to grow dramatically. Switching to a non‑auto‑extendable tablespace allows EXPIRED EXTENTs to be reused but increases the risk of ORA‑01555 and space‑shortage errors.

5. Guarantee Attribute

Setting the UNDO tablespace to guarantee ensures that undo data is retained for the full UNDO_RETENTION period, even under heavy load, preventing other transactions from stealing space.

6. Sizing the UNDO Tablespace

UNDO size depends on workload, UNDO_RETENTION, and the guarantee attribute. DBAs can estimate required size using statistics from V$UNDOSTAT.

7. Monitoring UNDO Usage

Key views for daily monitoring include:

DBA_ROLLBACK_SEGS – rollback segment definitions.

V$ROLLSTAT – rollback segment statistics.

V$TRANSACTION – active transactions.

V$UNDOSTAT – 10‑minute interval statistics (up to 576 rows, covering a 4‑day cycle).

DBA_UNDO_EXTENTS – status and size of each undo extent.

Extent status values:

ACTIVE – in use by uncommitted transactions.

EXPIRED – committed and beyond UNDO_RETENTION.

UNEXPIRED – committed but still within UNDO_RETENTION.

Oracle reuses extents according to the following rules:

ACTIVE extents are never reclaimed.

In auto‑extendable tablespaces, Oracle guarantees retention of EXPIRED extents for the configured period.

If space is insufficient, Oracle attempts to reuse EXPIRED extents from the same or other segments, then UNEXPIRED extents, before raising an error.

8. Analyzing V$UNDOSTAT Columns

Important columns include: UNDOTSN – active undo tablespace count. UNDOBLKS – total undo blocks consumed (useful for estimating required size). TXNCOUNT – number of transactions in the interval. MAXQUERYLEN – longest query duration (seconds), helps set UNDO_RETENTION. TUNED_UNDORETENTION – current retention value (seconds). SSOLDERRCNT – occurrences of ORA‑01555 (indicates retention mis‑configuration). NOSPACEERRCNT – space‑request failures (indicates insufficient UNDO space).

Typical interpretation: non‑zero UNXPSTEALCNT or EXPBLKREUCNT signals space pressure; non‑zero SSOLDERRCNT points to an unreasonable UNDO_RETENTION setting.

9. Releasing UNDO Tablespace

When an UNDO tablespace grows excessively, the common practice is to create a new UNDO tablespace, switch the instance to use it, and then drop the old one after all its segments become OFFLINE.

SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE 'E:\APP\ORADATA\ORCL3\undotbs02.dbf' SIZE 20M AUTOEXTEND ON NEXT 100M;

After confirming that all segments in the original tablespace are OFFLINE, drop it:

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Note that dropping the tablespace does not guarantee that all UNDO extents have expired; queries that still need those extents may raise ORA‑01555.