ORA-01555: snapshot too old
A long query needed old row versions from undo to stay read-consistent, but they had already been overwritten. Make the query faster or keep undo longer.
- Error code
ORA-01555- Category
- Space, memory and limits
What it means
Oracle gives each query a consistent view of the data as of its start, rebuilding changed rows from undo. If the query runs longer than undo is kept, the undo it needs may be gone and it fails with ORA-01555 (snapshot too old: rollback segment number string with name "string" too small).
Common causes
- A long-running query on tables that other sessions update heavily.
UNDO_RETENTIONshorter than the query's run time, or an undo tablespace too small to honor it.- Fetching across commits: committing inside a loop that reads from a cursor on the same table.
How to fix it
-
Speed up the query (better plan, indexes, less data) so it finishes within the undo retention.
-
Raise undo retention, and size the undo tablespace to keep it.
ALTER SYSTEM SET undo_retention = 3600; ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
-
Don't commit inside a loop over a cursor that reads the table being changed; commit after the loop.
Related errors
More space, memory and limits errors
ORA-01653unable to extend table in tablespaceORA-01950no privileges on tablespaceORA-04031unable to allocate bytes of shared memoryORA-01000maximum open cursors exceededORA-00018maximum number of sessions exceededORA-00020maximum number of processes exceededORA-00257archiver error
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
Work on Oracle in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.