Oracle problems

ORA-01555 Snapshot Too Old: Which of the Three Causes You Have, and the Fix for Each

2 November 2026

ORA-01555 fails a query after it ran for an hour, which is the most expensive moment to fail. The usual fix is to make the undo retention longer, and it is the right fix about a third of the time. The other two causes do not care how long the retention is.

What the error says

ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_1234567890$" too small. A query reads every block as it was when the query started. For a block that changed since, it needs the undo of that change, and the undo has been reused. The query fails. No transaction fails and no data is lost; the report just did not finish.

The same text with an empty segment name, rollback segment number with name "" too small, is the LOB case below, and it has nothing to do with the undo tablespace.

Which cause you have

Oracle writes the evidence to V$UNDOSTAT every ten minutes and keeps four days of it:

SELECT TO_CHAR(begin_time, 'DD HH24:MI') AS from_t,
       maxquerylen         AS longest_query_s,
       maxqueryid          AS longest_sql_id,
       tuned_undoretention AS tuned_retention_s,
       ssolderrcnt         AS ora_01555,
       unxpstealcnt        AS unexpired_taken,
       undoblks
FROM   v$undostat
WHERE  ssolderrcnt > 0
OR     begin_time > SYSDATE - 1 / 24
ORDER  BY begin_time DESC;

What the rows show	Cause	Fix
`LONGEST_QUERY_S` above `TUNED_RETENTION_S`, `UNEXPIRED_TAKEN` above zero	Not enough undo for the retention the queries need. Transactions took unexpired undo because the tablespace had nothing else.	More undo space, then the retention
`LONGEST_QUERY_S` in the hours, retention already long	The query. A report that runs for three hours needs three hours of undo for every table it touches, on a system that changes them all day.	The query, or its time of day
Retention and space fine, the error comes from a PL/SQL loop	The loop commits inside the cursor that reads the rows it changes, so it overwrites the undo its own cursor needs.	The loop
Empty segment name in the message	LOB undo. Old versions of a LOB live in the LOB segment itself, with their own retention, and `undo_retention` does not apply.	The LOB storage

MAXQUERYID names the query. V$SQL has its text while it is in the cache, and Scops keeps the statistics of every statement for 15 days, so the statement that failed at 02:00 is still there in the morning.

Fix it, by cause

Undo space and retention. Size the undo for the longest query plus a margin, at the rate UNDOBLKS shows: the arithmetic is on the undo tablespace page. Let the datafiles autoextend, then set undo_retention to the longest query. With a fixed size, Oracle ignores the parameter and tunes the retention to the space it has.
The query. A faster query needs less undo. Run the three-hour report at 03:00, when fewer blocks change, or make it read a consistent copy: a snapshot standby, a flashback query on a time of your choice, or an export of the tables it needs.
The loop. This is the one that comes back after every fix of the undo:
```
FOR r IN (SELECT rowid AS rid FROM app.orders
          WHERE  status = 'OLD') LOOP
  UPDATE app.orders SET status = 'ARCHIVED' WHERE rowid = r.rid;
  COMMIT;   -- frees the undo that the open cursor still needs
END LOOP;
```
Commit after the loop, or read the keys first with BULK COLLECT and change them in batches with a new query for each batch. Scops archives old rows in rowid chunks for the same reason: each chunk is its own query and its own transaction.
The LOB. A BasicFiles LOB keeps old versions by PCTVERSION of the segment, 10% by default; a SecureFiles LOB by its RETENTION. Raise it on the column: ALTER TABLE app.docs MODIFY LOB (body) (PCTVERSION 30); for BasicFiles, ALTER TABLE app.docs MODIFY LOB (body) (RETENTION MIN 3600); for SecureFiles.
A report that must finish. ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; makes queries win: unexpired undo is never taken, and a transaction that needs it fails with ORA-30036 instead. Use it when the report matters more than the transactions, and size the undo for it.

See it coming

ORA-01555 cannot be forecast from the undo alone, because it depends on which query runs when. What can be seen are its causes, and Radar Engine, the detection layer of Scops, reports them:

Detector	Clock	When it fires
ORA-01555 in the alert log	Live, with the OS credential of the database	A code repeated over three days: Warning. Five times: Critical.
Undo tablespace nearly full	Hourly, over 24 hours	High and held by an open transaction: Warning. Unexpired undo only: no finding.
Long-running transaction	Hourly	The largest open transaction at 3 hours, 2 GB of undo, and growing: Warning. 8 hours or 10 GB: Critical.
SQL slowing down	Daily	1.5 times slower per run for several days, at 100 ms and 30 runs or more: Warning. A report that slows down week by week meets the retention one day.

Questions

Does ORA-01555 lose data?
No. A query failed to finish; no transaction was affected. Run it again, after the fix.

Can I set undo_retention to a very large value?
Only with the space for it: the undo needed is the rate of change times the retention. With autoextend on, the parameter is the minimum Oracle keeps; with a fixed size it is ignored in favor of what the space allows, unless retention is guaranteed.

Why do I get ORA-01555 on a LOB column with plenty of undo?
Old versions of a LOB are kept in the LOB segment, under PCTVERSION for BasicFiles or RETENTION for SecureFiles, not in the undo tablespace. The message has an empty segment name in that case.

Why does a query fail right after a large load?
Blocks that a large committed transaction left without cleanout need that transaction's undo the first time they are read. A full scan of the table after the load, before the undo expires, clears it.

Sources

Oracle: ORA-01555
Oracle: V$UNDOSTAT, Managing Undo, LOB Storage
Scops docs: Detector reference