- Error code
- ORA-01555
- Product
- Oracle Database
- Versions
- Oracle Database 19c, 21c, 23ai and 26ai (same wording)
- Category
- Locking, Transactions & Sessions
- Last reviewed
- 24 October 2026
On this page
What it means
ORA-01555 means a query needed old versions of rows to give a consistent result, but the undo holding those versions had already been overwritten by other transactions, so the query failed.
Every Oracle query sees the data as it was when the query started. When rows change while the query is still running, Oracle rebuilds their earlier versions from undo (rollback) records. Oracle's reference gives the cause as: rollback records needed by a reader for consistent read are overwritten by other writers. Once committed undo has been reused, a long-running query that still needs it fails with ORA-01555. The segment number and name in the message identify the undo segment involved; they rarely help with the fix.
With automatic undo management, Oracle keeps committed undo for at least UNDO_RETENTION seconds (default 900) and tunes the actual retention, shown in V$UNDOSTAT.TUNED_UNDORETENTION. That minimum is honoured only while the undo tablespace has room: Oracle's reference says that when active transactions need undo space and none is free, the database starts reusing unexpired undo, which can cause "snapshot too old". For an autoextending undo tablespace Oracle extends it instead, until any MAXSIZE is reached.
So the fix is one of three things: make the query need undo for less time, keep undo longer (retention and enough space to honour it), or stop the query's own session from releasing the undo it still needs. The error is not about locks: no session is blocked, the reader simply runs out of history.
Common causes
- A long report or export query runs while other sessions keep changing the same table, and it outlives the undo retention.
- The undo tablespace is fixed in size or has reached its MAXSIZE, so busy transactions reuse undo before UNDO_RETENTION has passed.
- A PL/SQL loop fetches from a cursor and commits inside the loop while it changes the same table, so the undo the open cursor still needs becomes reusable.
- Flashback Query (AS OF TIMESTAMP) asks for a point further back than the undo retained.
- A query that is slow because of a poor execution plan, which turns an ordinary report into a long-running one.
Diagnose it
Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.
How often does it happen, and how long are the longest queries?
V$UNDOSTAT keeps one row per 10-minute interval for the last 4 days. SSOLDERRCNT counts ORA-01555 errors, MAXQUERYLEN is the longest query in seconds, and TUNED_UNDORETENTION is the retention Oracle actually kept. Needs SELECT access to V$ views, normally a DBA.
SELECT begin_time, end_time, maxquerylen, maxqueryid,
tuned_undoretention, ssolderrcnt, nospaceerrcnt, unxpstealcnt
FROM v$undostat
WHERE begin_time > SYSDATE - 1
ORDER BY begin_time DESC;If MAXQUERYLEN in the intervals with errors is larger than TUNED_UNDORETENTION, queries are outliving the undo. A non-zero UNXPSTEALCNT means unexpired undo was taken because space ran short.
Which query was the longest?
SELECT sql_id, elapsed_time / 1e6 AS elapsed_seconds, executions, sql_text
FROM v$sql
WHERE sql_id = '4ztz048yfq32s'; -- a MAXQUERYID value from V$UNDOSTATThe text and run time of the statement that ran longest. It is often the one failing, or the batch job that changes the table it reads.
Undo settings and space
SELECT name, value
FROM v$parameter
WHERE name IN ('undo_management', 'undo_tablespace', 'undo_retention');
SELECT t.tablespace_name, t.retention, f.file_name,
f.bytes / 1048576 AS size_mb, f.autoextensible, f.maxbytes / 1048576 AS max_mb
FROM dba_tablespaces t
JOIN dba_data_files f ON f.tablespace_name = t.tablespace_name
WHERE t.contents = 'UNDO';The retention target, whether the undo files can grow and how far, and whether RETENTION GUARANTEE is set (RETENTION column). Undo files that are at their maximum size cannot keep undo longer.
How to fix it
Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.
Do not commit inside a loop that reads the table it changes
Changes dataCommitting while a cursor on the same table is still open lets Oracle reuse the undo that cursor needs. Do the work as one set-based statement where you can, or commit after the loop has finished.
-- instead of a cursor loop that commits every 1000 rows:
UPDATE shop.orders
SET status = 'ARCHIVED'
WHERE order_date < DATE '2025-01-01';
COMMIT;Make the query shorter, or run it at a quieter time
Read-onlyA query that needs undo for less time is less likely to fail. Tune the slow statement (check its execution plan and indexes), read only the rows and columns you need, and schedule large reports away from heavy batch updates to the same tables.
Raise UNDO_RETENTION to cover the longest query
Changes configurationOracle's Action for automatic undo management is to increase UNDO_RETENTION. Set it to a little more than the longest MAXQUERYLEN you need to support. In a CDB, Oracle's reference explains that PDBs in local undo mode have their own value.
Caution: A longer retention needs more undo space. If the undo tablespace cannot grow, Oracle still reuses unexpired undo when space runs short, so check space first (next fix). Changing an instance parameter affects every session; agree it with the DBA.
ALTER SYSTEM SET undo_retention = 3600 SCOPE = BOTH;Give the undo tablespace room to honour the retention
Changes configurationFor an autoextending undo tablespace Oracle extends it rather than overwrite unexpired undo, up to MAXSIZE. Allow the undo data file to grow (or add one) within the disk space you have. Oracle also offers RETENTION GUARANTEE, which never overwrites unexpired undo; its documentation warns that this can make DML fail for lack of undo space, so use it only with care.
Caution: Make sure the file system has the space. The file name is an example: take yours from DBA_DATA_FILES.
ALTER DATABASE DATAFILE '/u01/oradata/FREE/undotbs01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 8G;Examples
A long report while a batch job updates the same table
Not reproduced here: no Oracle Database is installed for this site. The message format is from Oracle's error reference; the segment number and name are illustrative. Whether the report fails depends on the undo retention and space and on how much other work runs: the error is not deterministic.
-- session 1: a report that takes 40 minutes
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;
-- session 2, meanwhile: many committed updates to ORDERS
UPDATE orders SET status = 'INVOICED' WHERE status = 'SHIPPED';
COMMIT;ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_2387611590$" too small
Committing inside a cursor loop over the same table
Not reproduced here: no Oracle Database is installed for this site. The message format is from Oracle's error reference; the segment details and the ORA-06512 line number are illustrative, and the failure depends on how fast the undo is reused.
BEGIN
FOR r IN (SELECT id FROM orders WHERE status = 'NEW') LOOP
UPDATE orders SET status = 'QUEUED' WHERE id = r.id;
COMMIT; -- releases undo the open cursor still needs
END LOOP;
END;
/ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7_2387611590$" too small ORA-06512: at line 2
How to prevent it
Size undo for your longest queries: watch MAXQUERYLEN, TUNED_UNDORETENTION and SSOLDERRCNT in V$UNDOSTAT, and let the undo tablespace autoextend to a sensible MAXSIZE. Oracle generally recommends leaving UNDO_RETENTION at its default unless you need longer, for example for Flashback Query.
Write batch jobs as set-based statements or commit only after a cursor is closed, keep reports efficient, and schedule the heaviest reports and the heaviest updates on the same tables at different times.
Common questions
What does snapshot too old mean in Oracle?
The query needed undo to rebuild rows as they were when it started, but that undo had already been reused by other transactions. The query cannot give a consistent result, so it fails.
Will increasing UNDO_RETENTION always fix ORA-01555?
Only if the undo tablespace has the space to keep undo that long. When space runs out, Oracle reuses unexpired undo anyway (unless RETENTION GUARANTEE is set, which can make DML fail instead). Check space as well as the parameter.
Is ORA-01555 a locking problem?
No. Nothing is blocked: Oracle queries do not take row locks. The reader runs out of undo history, so the fixes are about query length, undo retention and undo space.
Learn more on CodeWithSQL
Official documentation
- ORA-01555 (Oracle Database Error Messages)
- UNDO_RETENTION (Oracle Database Reference 19c)
- Managing Undo (Oracle Database Administrator's Guide 19c)
- V$UNDOSTAT (Oracle Database Reference 19c)
Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.