Oracle’s undo tablespace holds the before-images of changed blocks for transaction rollback and read consistency. A session can undo its own work, PMON can roll back a dead session, and a long-running query can see the database as it existed at the query’s start SCN. Those uses compete for the same finite space, and the three extent states in DBA_UNDO_EXTENTS (ACTIVE, UNEXPIRED, EXPIRED) track which extents can be reused and which cannot.

“Undo tablespace full” is not a single failure. There are two distinct modes with opposite root causes and opposite fixes:

  • ORA-30036 (“unable to extend segment in undo tablespace”) is a write-path failure. New DML cannot allocate undo and the transaction fails. The cause is ACTIVE extents consuming the whole tablespace (or RETENTION GUARANTEE protecting UNEXPIRED extents under pressure).
  • ORA-01555 (“snapshot too old”) is a read-path failure. A long-running query needs undo that was already overwritten. The cause is retention being too short or being violated under pressure.

What it is and why it matters

The undo tablespace (typically UNDOTBS1) is managed by Automatic Undo Management (AUM, the default since 9i). Every DML statement writes a before-image of the affected block into an undo extent owned by the transaction until commit. After commit, the undo is no longer needed for rollback but remains needed for read-consistency queries that started before the commit.

DBA_UNDO_EXTENTS.STATUS tracks three states:

StatusMeaningCan be reused?
ACTIVEExtent belongs to an uncommitted transactionNo (rollback path)
UNEXPIREDTransaction committed, but undo is younger than the tuned retention periodOnly under space pressure, unless RETENTION GUARANTEE
EXPIREDUndo older than the tuned retention periodYes (first choice for reuse)

ACTIVE undo is non-negotiable. You cannot reuse it without breaking transactional integrity. UNEXPIRED undo is the buffer that protects long-running readers. EXPIRED undo is free for the taking.

When you sum these three groups, you get the actual pressure profile of your undo tablespace:

  • EXPIRED-dominated: healthy. Space is reclaimable.
  • UNEXPIRED-dominated: retention is high relative to commit rate. Possibly a long query is inflating tuned retention.
  • ACTIVE-dominated: live transactions are consuming most of the tablespace. This is the PAGE-risk pattern.

How it works

Each undo extent moves through a state machine. The transitions are deterministic, but the timing of the UNEXPIRED-to-EXPIRED transition depends on TUNED_UNDORETENTION, which Oracle adjusts dynamically.

stateDiagram-v2
    [*] --> ACTIVE: transaction allocates extent
    ACTIVE --> UNEXPIRED: transaction commits
    UNEXPIRED --> EXPIRED: age > TUNED_UNDORETENTION
    EXPIRED --> ACTIVE: reused by new transaction
    UNEXPIRED --> ACTIVE: stolen under pressure (UNXPSTEALCNT++)

UNDO_RETENTION is a request, not a guarantee

UNDO_RETENTION (default 900 seconds) is the operator’s requested minimum retention for committed undo. Oracle’s auto-tuning algorithm sets TUNED_UNDORETENTION, the actual value used to decide when an UNEXPIRED extent becomes EXPIRED. TUNED_UNDORETENTION is visible in V$UNDOSTAT.

For autoextensible undo tablespaces, Oracle tunes TUNED_UNDORETENTION to be at least UNDO_RETENTION, and typically somewhat longer than the longest-running query in the interval (MAXQUERYLEN). This is what produces the common surprise: a single long-running report inflates TUNED_UNDORETENTION for the whole tablespace, and extents that should have expired stay UNEXPIRED for hours or days.

For fixed-size undo tablespaces, behavior depends on patch level. Before 19c Release Update 19.7, fixed-size tablespaces largely ignored UNDO_RETENTION and tuned retention to the maximum the tablespace could sustain. From 19.7 onward, fixed-size tablespaces honor UNDO_RETENTION as a minimum threshold and auto-tune above it.

RETENTION GUARANTEE changes the failure mode

By default, undo is best-effort. Under space pressure, Oracle will steal UNEXPIRED extents (incrementing UNXPSTEALCNT in V$UNDOSTAT) before failing a transaction. The RETENTION GUARANTEE tablespace attribute removes that escape hatch: UNEXPIRED extents are never reused, even if it means new transactions fail.

Check the current setting:

-- Check RETENTION GUARANTEE status
SELECT TABLESPACE_NAME, RETENTION FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';
-- NOGUARANTEE = default; GUARANTEE = strict retention, can cause ORA-30036

The tradeoff is direct: RETENTION GUARANTEE trades ORA-01555 (readers fail) for ORA-30036 (writers fail). For most OLTP systems, ORA-01555 affecting a few long reports is preferable to DML failing across the board, which is why the default is NOGUARANTEE. Guarantee is typically used when you have flashback or long-running extract requirements that cannot tolerate snapshot-too-old.

What happens when undo runs out

When a transaction needs undo and there is no EXPIRED extent available, Oracle walks through several allocation attempts before failing:

  • Reuse EXPIRED extents from any undo segment (normal path)
  • Autoextend the datafile if enabled and below MAXSIZE
  • Steal UNEXPIRED extents from other segments, if RETENTION GUARANTEE is not set (increments UNXPSTEALCNT)
  • Fail the transaction with ORA-30036 (increments NOSPACEERRCNT)

The exact intermediate steps vary by version. The important operational point: UNXPSTEALCNT rising is the last warning before NOSPACEERRCNT, and RETENTION GUARANTEE removes the steal step entirely, converting UNEXPIRED pressure directly into ORA-30036.

Where it shows up in production

The undo pressure spiral

A large batch job (millions of rows updated in a single transaction, no intermediate commits) generates enormous ACTIVE undo. Concurrently, long-running reports need UNEXPIRED undo for read consistency. ACTIVE extents fill the tablespace, UNEXPIRED extents are stolen (UNXPSTEALCNT rises), readers start hitting ORA-01555, and if ACTIVE undo eventually consumes everything, DML starts failing with ORA-30036.

The diagnostic signature:

  • DBA_UNDO_EXTENTS shows ACTIVE » UNEXPIRED + EXPIRED
  • V$TRANSACTION shows one or more transactions with very high USED_UBLK
  • V$UNDOSTAT.UNXPSTEALCNT rising, then NOSPACEERRCNT rising
  • Alert log shows ORA-01555 first, then ORA-30036 if the spiral continues

Silent retention inflation

On a non-autoextensible undo tablespace with light load, TUNED_UNDORETENTION can grow very large (200,000+ seconds has been observed in the field, per MOS Doc ID 1112431.1). Extents never expire, UNEXPIRED dominates the tablespace, and operators see “undo tablespace full” with very little ACTIVE undo. This is the opposite problem: not too many transactions, but retention that has tuned itself beyond what the operator intended.

Workarounds include enabling autoextend, allocating more space, or capping the tuned value via the underscore parameter _highthreshold_undoretention (use with caution and only after confirming the symptom). Disabling auto-tune (_undo_autotune=false) is generally not recommended because it removes Oracle’s ability to adapt to workload changes.

The long query that breaks retention

A single very long-running query can inflate TUNED_UNDORETENTION far above the configured UNDO_RETENTION, because the auto-tuning algorithm tracks MAXQUERYLEN. If a four-hour report runs during peak OLTP, undo extents will be retained for at least four hours even if UNDO_RETENTION is set to 900. The symptom is UNEXPIRED undo growing without a corresponding increase in ACTIVE undo, and no obvious transactional spike.

Tradeoffs and when to use it

DecisionDefaultWhen to change itTradeoff
RETENTION GUARANTEENOGUARANTEEWhen ORA-01555 is unacceptable (flashback, compliance extracts)Converts ORA-01555 risk into ORA-30036 risk
Autoextend on datafilesVariesWhen undo usage is spiky or unpredictableHides sizing problems; can fill filesystem
Increase UNDO_RETENTION900 secondsWhen long-running reports regularly hit ORA-01555Increases UNEXPIRED footprint
Increase undo tablespace size-When ACTIVE undo regularly exceeds 60% of tablespace at peakStorage cost
SHRINK TABLESPACE (23ai)n/aWhen undo grew for a one-off job and never shrankOne-time reclamation; does not change lifecycle

Size undo for your peak ACTIVE footprint plus headroom for UNDO_RETENTION worth of committed undo. If ACTIVE undo alone exceeds 85% of the tablespace at peak, you are at PAGE risk regardless of retention settings.

On RAC, each instance has its own undo tablespace. Monitor each independently. A pressure spiral on one instance will not be visible in another instance’s V$UNDOSTAT (use GV$UNDOSTAT for a cluster-wide view).

Signals to watch in production

SignalWhy it mattersWarning sign
DBA_UNDO_EXTENTS by STATUSShows the actual composition of undo pressureACTIVE > 60% of total at peak
V$UNDOSTAT.NOSPACEERRCNTCounts ORA-30036 occurrences per 10-minute intervalAny value > 0
V$UNDOSTAT.SSOLDERRCNTCounts ORA-01555 occurrences per 10-minute intervalAny value > 0
V$UNDOSTAT.UNXPSTEALCNTCounts unexpired-extent steals (retention violated)Sustained > 0
V$UNDOSTAT.TUNED_UNDORETENTIONActual retention in use, may exceed UNDO_RETENTIONSpikes correlated with long queries
V$UNDOSTAT.MAXQUERYLENLongest query in the interval, in secondsApproaching or exceeding UNDO_RETENTION
V$UNDOSTAT.ACTIVEBLKS / UNEXPIREDBLKS / EXPIREDBLKSPer-interval block counts by stateACTIVEBLKS dominating
DBA_TABLESPACES.RETENTIONWhether GUARANTEE is setChanges the failure mode (ORA-30036 vs ORA-01555)
V$TRANSACTION.USED_UBLKLive undo consumption per active transactionSingle transaction with very high value
Tablespace utilization (DBA_TABLESPACE_USAGE_METRICS)Overall fill level> 85% of max capacity

The two earliest leading indicators, before any error occurs, are UNXPSTEALCNT > 0 and ACTIVE undo growing as a percentage of total. Both mean undo pressure is building and the cliff (ORA-30036) is approaching. Severity ladder:

  • Any NOSPACEERRCNT > 0: PAGE (ORA-30036 occurring, writes failing)
  • Any SSOLDERRCNT > 0: TICKET (ORA-01555 occurring, reads failing)
  • UNXPSTEALCNT consistently > 0: PLAN (retention not being honored, no outright failure yet)

How Netdata helps

  • Per-second collection from DBA_TABLESPACE_USAGE_METRICS, so you see the fill rate rather than a point-in-time snapshot.
  • Correlation of V$UNDOSTAT counters (UNXPSTEALCNT, NOSPACEERRCNT, SSOLDERRCNT) with transaction throughput and redo rate, to distinguish a batch-transaction spike from retention tuning.
  • DBA_UNDO_EXTENTS breakdown by STATUS as separate chart dimensions, so ACTIVE-dominated and UNEXPIRED-dominated pressure are visually distinct.
  • Alerting on NOSPACEERRCNT > 0 (PAGE) and SSOLDERRCNT > 0 (TICKET), so the two undo failure modes page differently.
  • Anomaly detection on TUNED_UNDORETENTION, which catches silent retention inflation where the value drifts far above the configured UNDO_RETENTION.
  • Long-history retention of MAXQUERYLEN against UNDO_RETENTION for capacity planning.