ORA-01555: snapshot too old, rollback segment too small

ORA-01555 is an Oracle read-path failure. A long-running query needs an old undo version of a block that has already been overwritten, so the query errors out:

ORA-01555: snapshot too old (rollback segment too small)

The “rollback segment” wording is a legacy artifact. Automatic Undo Management (AUM) was introduced in Oracle 9i; modern Oracle strongly recommends automatic undo management and supports manual rollback-segment mode mainly for backward compatibility. The actual cause is undo pressure in an AUM undo tablespace. Treating this as a “rollback segment” problem leads down the wrong path.

ORA-01555 is a read-path failure, distinct from ORA-30036 (“unable to extend undo segment”), which is a write-path failure. With ORA-01555, only the long-running query fails; DML keeps succeeding. With ORA-30036, writes themselves fail because the undo tablespace is full of ACTIVE extents. Severity differs accordingly: ORA-01555 is a TICKET, ORA-30036 is a PAGE because it blocks production writes.

What this means

Oracle provides statement-level read consistency through undo. When a SELECT starts at SCN 100, every block it reads must be reconstructable to its state at SCN 100. The database reconstructs older versions of blocks using undo records written when DML modified them. As long as the undo records for the relevant SCNs are still in the undo tablespace, read consistency works.

The failure happens when undo needed by an old query is overwritten:

1. Query Q starts at SCN 100.
2. While Q runs, transaction T modifies block B and writes undo records.
3. The undo tablespace fills with ACTIVE undo from T and other concurrent transactions.
4. Under the default RETENTION NOGUARANTEE, Oracle reuses UNEXPIRED undo extents (undo still within the retention target) to make room for active transactions.
5. Q eventually reaches block B and tries to reconstruct it as of SCN 100. The undo it needs was overwritten. ORA-01555.

flowchart TD
    Start[Long query starts at SCN 100]
    DML[Concurrent DML modifies block B]
    Undo1[Undo record for B written to undo tablespace]
    Fill[Undo tablespace fills with ACTIVE undo]
    Steal[Under NOGUARANTEE, unexpired undo reused]
    Over[Undo record for B overwritten]
    Read[Query reads block B]
    Recon[Needs undo record for SCN 100]
    Err[ORA-01555: snapshot too old]
    Start --> DML --> Undo1 --> Fill --> Steal --> Over
    Read --> Recon
    Recon -. Looks for record .-> Over
    Over --> Err

Two properties of UNDO_RETENTION make this failure more common than operators expect. First, UNDO_RETENTION is a target, not a guarantee. Under RETENTION NOGUARANTEE (the default), if the undo tablespace runs out of free space, Oracle overwrites unexpired undo to keep active transactions running, even if that breaks read consistency. Second, in a fixed-size undo tablespace Oracle tunes the actual retention to whatever the tablespace can support, which may be less than UNDO_RETENTION. The number to watch is V$UNDOSTAT.TUNED_UNDORETENTION, not the parameter value.

Common causes

Quick checks

These are read-only and safe to run any time.

# Confirm which undo error is firing (01555 read path vs 30036 write path)
adrci exec="show alert -tail 200" | grep -Ei "ORA-01555|ORA-30036"

-- SSOLDERRCNT counts ORA-01555 per 10-min interval; NOSPACEERRCNT counts ORA-30036
SELECT BEGIN_TIME, END_TIME, MAXQUERYLEN, UNXPSTEALCNT,
       EXPSTEALCNT, SSOLDERRCNT, NOSPACEERRCNT
FROM V$UNDOSTAT
WHERE BEGIN_TIME > SYSDATE - 1
ORDER BY BEGIN_TIME DESC;

-- Undo extent status breakdown
SELECT STATUS, SUM(BYTES)/1048576 AS mb
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS
ORDER BY mb DESC;
-- ACTIVE: live transactions; UNEXPIRED: within retention, reclaimable; EXPIRED: free

-- Largest current undo consumers
SELECT s.SID, s.SERIAL#, s.USERNAME, s.SQL_ID,
       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;

-- Tuned retention vs configured retention
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'undo_retention';
SELECT BEGIN_TIME, TUNED_UNDORETENTION, MAXQUERYLEN
FROM V$UNDOSTAT
WHERE BEGIN_TIME > SYSDATE - 1
ORDER BY BEGIN_TIME DESC;

-- Check retention guarantee status on the undo tablespace
SELECT TABLESPACE_NAME, RETENTION
FROM DBA_TABLESPACES
WHERE CONTENTS = 'UNDO';
-- NOGUARANTEE = default; GUARANTEE = strict, can convert ORA-01555 to ORA-30036

How to diagnose it

1. Confirm the error is ORA-01555 and not ORA-30036. They share root cause (undo pressure) but ORA-30036 means writes are failing, which is more urgent. Search the alert log for both codes.
2. Find the interval where SSOLDERRCNT spiked in V$UNDOSTAT. The 10-minute row tells you when the read-path failure happened and what the workload looked like at that moment.
3. Compare UNXPSTEALCNT to EXPSTEALCNT in the same interval. UNXPSTEALCNT > 0 means Oracle is reclaiming undo still within the retention target, the strongest predictor of ORA-01555. EXPSTEALCNT > 0 alone is normal pressure.
4. Look at MAXQUERYLEN. If it regularly exceeds UNDO_RETENTION or TUNED_UNDORETENTION, your undo budget is structurally smaller than the longest queries.
5. Identify the largest undo consumer from V$TRANSACTION ordered by USED_UBLK. A single massive UPDATE or DELETE is the usual suspect. Cross-reference with SQL_ID to find the offending statement.
6. Check whether the failing query touches LOB columns. LOBs do not honor automatic undo tuning. RETENTION is fixed at LOB creation time based on UNDO_RETENTION at that moment, and changing UNDO_RETENTION afterward does not update existing LOB segments.
7. Rule out delayed block cleanout. If a bulk load or direct-path operation was committed without cleanout and a long scan reads those blocks afterward, ORA-01555 can occur with little concurrent DML. Look for the pattern in recent load jobs and V$SESSION_LONGOPS.

Metrics and signals to monitor

Fixes

Pick the fix that matches the cause. Adding undo space is the most reliable lever but takes storage planning. Killing a runaway transaction is the fastest intervention but rolls back work.

Add undo tablespace space

Add a datafile or enable autoextend on the existing undo datafile. Safest fix when storage is available.

-- Add a datafile to the undo tablespace (replace names and size)
ALTER TABLESPACE UNDOTBS1
  ADD DATAFILE '/u01/oradata/undotbs1_02.dbf'
  SIZE 4G AUTOEXTEND ON NEXT 256M MAXSIZE 32G;

Tradeoff: more disk consumed. Verify the underlying filesystem or ASM disk group has the headroom.

Increase UNDO_RETENTION

If MAXQUERYLEN regularly exceeds UNDO_RETENTION, raise the parameter.

ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=BOTH;

In a fixed-size undo tablespace, Oracle ignores UNDO_RETENTION unless RETENTION GUARANTEE is enabled, tuning instead to the maximum retention the tablespace can support. Raising UNDO_RETENTION without adding space achieves nothing in that mode. With autoextend-enabled tablespaces, Oracle honors UNDO_RETENTION as a minimum but still needs free space to grow into.

In multitenant local-undo mode from Release Update 19.9 onward, UNDO_RETENTION is not inherited through the CDB; set it explicitly in each PDB.

Enable RETENTION GUARANTEE

Trade the ORA-01555 failure mode for the ORA-30036 failure mode.

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

With GUARANTEE, Oracle will not steal UNEXPIRED undo, so reads stop failing with ORA-01555. But active transactions can hit ORA-30036 when the tablespace fills with unexpired extents. Use this when read consistency is more important than write availability, and pair it with adequate space.

Kill the runaway transaction

If V$TRANSACTION shows one session holding disproportionate undo, killing it releases the undo immediately. Disruptive: rolls back the transaction, which can itself generate more undo and redo.

-- Identify first
SELECT s.SID, s.SERIAL#, s.USERNAME, s.SQL_ID, t.USED_UBLK
FROM V$TRANSACTION t
JOIN V$SESSION s ON t.SES_ADDR = s.SADDR
ORDER BY t.USED_UBLK DESC;
-- Then kill (disruptive, rolls back the transaction)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Rollback on a very large transaction can be slow. Verify with the business owner before killing.

Fix LOB retention

LOB segments do not honor changes to UNDO_RETENTION after creation. If queries fail on tables with LOB columns, the LOB RETENTION may be stale.

-- Check current LOB retention
SELECT OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME, RETENTION
FROM DBA_LOBS
WHERE OWNER = '<schema>';

Changing LOB retention requires changing the LOB storage metadata or rebuilding the segment. The standard approach is ALTER TABLE <table> MOVE LOB(<lob_column>) STORE AS ... (RETENTION ...);; verify its DML impact for the object type. Online redefinition is the documented alternative for SecureFiles LOBs.

Adjust the workload

• Split large batch transactions into smaller pieces with intermediate commits. Counterintuitively, frequent commits inside a single SELECT loop can worsen ORA-01555 because each commit marks undo as expired sooner, making it eligible for reuse. The fix is to commit less often in batch DML and not at all in a read-only cursor.
• Move long-running reports to an Active Data Guard standby. They consume undo on the standby instead of the primary. Note that Active Data Guard has its own ORA-01555 pattern: Oracle documents increasing UNDO_RETENTION on the primary to accommodate standby-query retention requirements.
• Consider TEMP_UNDO_ENABLED=TRUE (12c+) if reports read global temporary tables. Temporary undo is separated from permanent undo and reduces pressure on the undo tablespace.

Prevention

• Trend UNXPSTEALCNT daily. Any sustained non-zero value is a leading indicator before SSOLDERRCNT goes non-zero.
• Track MAXQUERYLEN vs UNDO_RETENTION weekly. If queries routinely exceed retention, either raise retention or shorten queries.
• Monitor ACTIVE undo as a percentage of the undo tablespace. Aim for under 60% at peak.
• Size the undo tablespace from history, not by guessing. Use AWR or V$UNDOSTAT history combined with peak undo generation rate and target retention.
• Standardize on RETENTION GUARANTEE only with comfortable headroom. Otherwise it converts ORA-01555 into ORA-30036.
• Capture UNDO_RETENTION into build scripts for LOB-heavy schemas. New LOBs pick up the value at creation time. Review LOB retention whenever you change UNDO_RETENTION.
• Document expected transaction sizes for batch jobs. Alarm on USED_UBLK thresholds so runaway transactions surface before they hold undo for hours.

How Netdata helps

• Per-second undo tablespace utilization and growth rate catch pressure before SSOLDERRCNT goes non-zero.
• V$UNDOSTAT signals (SSOLDERRCNT, UNXPSTEALCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION) plotted together show the moment pressure turns into read failures.
• Correlating UNDO_RETENTION and TUNED_UNDORETENTION against MAXQUERYLEN makes the structural mismatch obvious before users complain.
• Alert log ORA-01555 and ORA-30036 counts cross-reference with undo metrics so you confirm cause in one view.
• Top undo consumers from V$TRANSACTION highlight runaway sessions without a manual query during incidents.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second collection.

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
• Oracle redo generation rate: capacity planning for archiving and Data Guard
• Oracle redo log switch frequency: undersized logs and checkpoint pressure