Oracle undo tablespace usage: ACTIVE vs UNEXPIRED vs EXPIRED and UNDO_RETENTION

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:

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 release level. Oracle documents that, starting with 19c Release Update 19.7, both autoextensible and fixed-size undo tablespaces retain undo for at least the UNDO_RETENTION time and auto-tune above it for query requirements. Before that release level, fixed-size behavior was to tune for the maximum sustainable retention and ignore UNDO_RETENTION unless retention was guaranteed; do not generalize the old behavior to a 19.7+ or 23ai system.

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 cited MOS note is symptom- and release-specific; verify the current 19c/23ai patch guidance through My Oracle Support before applying an underscore parameter or changing datafile autoextend behavior.

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

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

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.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE
• Oracle ’log file sync’ waits: slow commits, LGWR, and the redo path
• Oracle Database monitoring checklist: the signals every production instance needs
• Oracle Database monitoring maturity model: from survival to expert
• ORA-00257: archiver error, connect internal only until freed
• ORA-01653: unable to extend table in tablespace
• ORA-01654: unable to extend index in tablespace
• Oracle redo generation rate: capacity planning for archiving and Data Guard