Oracle undo pressure spiral: long transactions, long queries, and ORA-01555/30036

ORA-01555 (“snapshot too old”) and ORA-30036 (“unable to extend undo segment”) are two faces of the same pressure: undo extents are being consumed faster than they can be reclaimed or retained. In production they usually arrive together, in a spiral where one large uncommitted transaction starves a fleet of long-running queries, then starves new writes.

The classic trigger is a batch job that updates or deletes millions of rows in a single transaction with no intermediate commits. It generates enormous undo. At the same time, reports and ETL reads need older undo blocks for read consistency. The undo tablespace fills with ACTIVE extents that cannot be reclaimed. Under the default RETENTION NOGUARANTEE, Oracle starts stealing UNEXPIRED undo to make room, and the long reads start failing with ORA-01555. If ACTIVE undo fills everything, new DML fails outright with ORA-30036.

The diagnostic core is one query: join V$TRANSACTION.USED_UBLK to V$SESSION, sort descending. Everything else is confirmation.

What this means

Every data change in Oracle generates redo (for recovery) and undo (for rollback and read consistency). Undo records live in the undo tablespace. Each undo extent is in one of three states:

• ACTIVE: needed by an in-flight transaction. Cannot be reclaimed until the transaction commits or rolls back.
• UNEXPIRED: older than the active transaction but younger than UNDO_RETENTION. Kept to satisfy long-running queries that need a consistent snapshot. Reclaimable under pressure if the tablespace is RETENTION NOGUARANTEE, which is the default.
• EXPIRED: older than UNDO_RETENTION. Free for reuse.

UNDO_RETENTION (default 900 seconds) is a target, not a guarantee. Oracle auto-tunes the actual retention based on tablespace size and workload. With a fixed-size, NOGUARANTEE tablespace, Oracle can auto-tune TUNED_UNDORETENTION below the parameter value under space pressure. RETENTION GUARANTEE does not force the parameter to be honored; it prevents stealing of UNEXPIRED extents. For an autoextensible tablespace, Oracle attempts to honor the parameter and grows the datafiles when space is low. Read the value the system is actually targeting from V$UNDOSTAT.TUNED_UNDORETENTION.

The spiral has two distinct failure modes:

• ORA-01555 (read-path failure): a long-running query needs undo that has already been overwritten. Read consistency is lost, the query fails. Catalogued in V$UNDOSTAT.SSOLDERRCNT.
• ORA-30036 (write-path failure): a DML statement cannot allocate a new undo extent. The transaction fails. Catalogued in V$UNDOSTAT.NOSPACEERRCNT.

ORA-01555 is the early warning. ORA-30036 is the cliff.

flowchart TD
    A[Large uncommitted transaction] --> B[Undo extents marked ACTIVE]
    B --> C[Undo tablespace fills with ACTIVE]
    C --> D{Free space for new undo?}
    D -- Steal UNEXPIRED --> E[Long reads lose their snapshot]
    E --> F[ORA-01555 SSOLDERRCNT]
    D -- No, all ACTIVE --> G[New DML cannot allocate undo]
    G --> H[ORA-30036 NOSPACEERRCNT]
    F --> I[Reads retry, pressure rises]
    I --> C

Common causes

Quick checks

All read-only and safe to run during an incident.

-- 1. Undo extent status breakdown
SELECT STATUS, SUM(BYTES)/1048576 AS mb
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS
ORDER BY mb DESC;
-- ACTIVE = unreclaimable, UNEXPIRED = retained but stealable,
-- EXPIRED = free for reuse

-- 2. Recent undo pressure and error counters (10-minute buckets)
SELECT BEGIN_TIME, END_TIME, UNDOBLKS, TXNCOUNT, MAXQUERYLEN,
       UNXPSTEALCNT, SSOLDERRCNT, NOSPACEERRCNT
FROM V$UNDOSTAT
WHERE BEGIN_TIME > SYSDATE - 1
ORDER BY BEGIN_TIME DESC;
-- SSOLDERRCNT   = ORA-01555 count in that bucket
-- NOSPACEERRCNT = ORA-30036 count in that bucket
-- UNXPSTEALCNT  = times unexpired undo was stolen

-- 3. Largest undo consumers right now
SELECT s.SID, s.SERIAL#, s.USERNAME, s.SQL_ID, s.EVENT,
       t.USED_UBLK * (SELECT VALUE FROM V$PARAMETER WHERE NAME='db_block_size') / 1048576 AS undo_mb
FROM V$TRANSACTION t
JOIN V$SESSION s ON t.SES_ADDR = s.SADDR
ORDER BY t.USED_UBLK DESC;

-- 4. Undo tablespace size vs autoextend headroom
SELECT df.TABLESPACE_NAME,
       ROUND(SUM(df.BYTES)/1048576,2) AS current_mb,
       ROUND(SUM(DECODE(df.AUTOEXTENSIBLE,'YES',df.MAXBYTES,df.BYTES))/1048576,2) AS max_mb,
       ROUND(SUM(DECODE(df.AUTOEXTENSIBLE,'YES',df.MAXBYTES,df.BYTES) - df.BYTES)/1048576,2) AS autoextend_headroom_mb
FROM DBA_DATA_FILES df
WHERE df.TABLESPACE_NAME LIKE 'UND%'
GROUP BY df.TABLESPACE_NAME;

-- 5. Is retention being honored at all?
SELECT BEGIN_TIME, MAXQUERYLEN,
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'undo_retention') AS undo_retention_param,
       TUNED_UNDORETENTION
FROM V$UNDOSTAT
WHERE BEGIN_TIME > SYSDATE - 1
ORDER BY BEGIN_TIME DESC;
-- If TUNED_UNDORETENTION is below undo_retention_param, the tablespace is forcing auto-tune down.

# 6. Alert log for ORA-01555 / ORA-30036 frequency
adrci exec="show alert -tail 500" | grep -E "ORA-01555|ORA-30036"

How to diagnose it

1. Confirm which side of the spiral you are on. Look at the most recent V$UNDOSTAT rows. Non-zero SSOLDERRCNT means ORA-01555 (reads failing). Non-zero NOSPACEERRCNT means ORA-30036 (writes failing). NOSPACEERRCNT is PAGE severity; SSOLDERRCNT alone is TICKET.

2. Look at the extent breakdown. If ACTIVE dominates and EXPIRED is near zero, the tablespace is held captive by in-flight transactions. If UNEXPIRED dominates and EXPIRED is near zero, retention is fighting space pressure and unexpired steal is happening or imminent.

3. Identify the culprit transaction. Run the “largest undo consumers” query. A single session with USED_UBLK an order of magnitude above the rest is your spiral driver. Note the SQL_ID, the EVENT, and whether the session is ACTIVE or INACTIVE.

4. Check whether retention is being honored. Compare TUNED_UNDORETENTION to the UNDO_RETENTION parameter. If the tuned value is much lower, the tablespace size is forcing Oracle to auto-tune down. Increasing UNDO_RETENTION without adding space will not help.

5. Compare longest query duration to retention. MAXQUERYLEN is the longest query (in seconds) running in each V$UNDOSTAT bucket. If it regularly exceeds UNDO_RETENTION or TUNED_UNDORETENTION, ORA-01555 is structural, not transient.

6. Decide whether to kill the runaway transaction. If one session is responsible and killing it is acceptable to the business, that is the fastest way to break the spiral. Otherwise, add undo space and wait for the transaction to commit on its own.

Metrics and signals to monitor

Fixes

Kill or commit the runaway transaction

If a single batch transaction is driving the spiral and the business can tolerate it, kill the session.

-- Identify the session first (use the "largest undo consumers" query above).
-- WARNING: this is disruptive. The transaction rolls back after the kill,
-- which keeps undo ACTIVE until rollback completes and generates redo.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Killing the transaction forces a rollback handled by PMON in the background. The undo extents stay ACTIVE until rollback completes; you do not reclaim space immediately, and a very large transaction can take a long time to roll back while continuing to generate pressure. Weigh this against leaving the transaction to commit naturally. If you cannot kill it, you must add undo space and wait.

Add undo space

The fastest mechanical fix when you cannot kill the transaction.

-- Add a datafile to the undo tablespace (replace name and size).
-- The directory must exist and be writable by the Oracle software owner.
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u01/oradata/undotbs1_02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- Or resize an existing datafile upward (only works if the OS file can grow):
ALTER DATABASE DATAFILE '/u01/oradata/undotbs1_01.dbf' RESIZE 20G;

Confirm autoextend is on and that MAXSIZE leaves real headroom. Use the tablespace utilization query from the quick checks to verify.

Adjust UNDO_RETENTION

Only useful if the tablespace has room to honor it. On a fixed-size, NOGUARANTEE tablespace, raising UNDO_RETENTION has no effect until you either add space or enable RETENTION GUARANTEE.

ALTER SYSTEM SET UNDO_RETENTION = 1800 SCOPE=BOTH;

On 19.9 and later with local undo enabled, UNDO_RETENTION is not inherited through the CDB. Set it explicitly in each PDB; setting it in CDB$ROOT alone is not a per-PDB configuration.

RETENTION GUARANTEE tradeoffs

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

RETENTION GUARANTEE converts ORA-01555 into ORA-30036. Long queries stop losing their snapshots, but DML fails when the tablespace is full instead of stealing unexpired undo. Use it only when read consistency matters more than write availability and the tablespace is sized to honor the retention target. To revert:

ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;

Fix the batch job

The root cause is almost always an application pattern: one transaction touching millions of rows with no intermediate commits. The structural fix is to commit in batches (every N thousand rows), use DBMS_PARALLEL_EXECUTE, or split the workload into smaller transactions. This is the only change that prevents the spiral from recurring.

Reschedule or isolate long-running reports

If MAXQUERYLEN regularly exceeds your achievable undo retention, move the reports to a non-overlapping window. When using Active Data Guard, Oracle documents increasing UNDO_RETENTION on the primary to accommodate standby-query retention requirements; validate standby undo space and long-report duration before offloading. Month-end reports on a busy OLTP system are the textbook case.

Prevention

• Treat UNXPSTEALCNT as the canary. Alert on any consistent non-zero value. It surfaces hours before SSOLDERRCNT or NOSPACEERRCNT.
• Size undo for peak, not average. ACTIVE undo should stay below 60% of the undo tablespace at peak.
• Avoid single-transaction batch jobs touching millions of rows. This is the most common spiral driver and the only one the application team can permanently fix.
• Track MAXQUERYLEN against TUNED_UNDORETENTION. If the longest query is creeping up against the tuned retention, you are one heavy DML window away from ORA-01555.
• Do not enable RETENTION GUARANTEE on an undersized tablespace. It moves the failure from reads to writes, which is usually worse.
• In RAC, monitor each instance’s undo tablespace independently. Pressure on one instance does not show up in the others.

How Netdata helps

• Correlate UNXPSTEALCNT, SSOLDERRCNT, and NOSPACEERRCNT from V$UNDOSTAT with active session count and the dominant wait class. A rising UNXPSTEALCNT at the same moment active sessions climb and enq: TX waits appear points at a specific transaction, not a general space shortage.
• Per-second undo tablespace utilization lets you see the ACTIVE fraction climbing in real time during a batch window, before the alert log fills with ORA-01555.
• Trend MAXQUERYLEN and TUNED_UNDORETENTION together so you can spot the gap closing days before a long report actually fails.
• Anomaly detection on USED_UBLK per session flags a runaway transaction the moment its undo footprint breaks the baseline, usually the first reliable signal that a spiral is starting.
• Cross-correlation with redo generation rate and log file sync helps distinguish an undo-driven slowdown from a redo-path or storage-latency problem with similar symptoms.

See Oracle Database monitoring with Netdata for the prebuilt dashboards and per-second collection that back these checks.

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 autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• 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-01555: snapshot too old, rollback segment too small
• ORA-01653: unable to extend table in tablespace