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

ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4249263262$" too small

A long-running query needed old row versions from undo, but other sessions' commits had already overwritten them.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-16

Oracle shows every query the data as of the moment it started. If other sessions change rows in the meantime, the old versions are rebuilt from undo. When the undo needed for that is already overwritten because the undo tablespace is too small, the query fails with ORA-01555, even though it did nothing wrong itself.

The error hits the long query, not the session doing the updates. In the lab the "slow report" is simulated with DBMS_SESSION.SLEEP(10) between the first and the remaining fetches.

Causes and fixes

  1. Undo tablespace too small for a long query while others commit

1Undo tablespace too small for a long query while others commit

Session A opened a cursor on orders (20,000 rows), fetched one row and paused 10 seconds. Meanwhile session B updated all 20,000 rows one at a time with a COMMIT after each. B finished normally; A then failed at its next FETCH (ORA-06512: at line 11) with ORA-01555.

The check showed undo_retention 900 seconds, but the undo tablespace LAB_UNDO was 1 MB with autoextend NO, so it could not keep 15 minutes of undo.

After ALTER DATABASE DATAFILE '...lab_undo01.dbf' RESIZE 100M, the same two sessions ran again. A read to the end and printed total = 2080000, the sum as of when its cursor opened. (The reproduce step had been run 4 times, once per message language, so amounts were already 104 each; B's +1 updates during the retry are not in A's total.)

Setup
Setuprun as system
ALTER SYSTEM SET undo_tablespace = undotbs1;
DROP TABLESPACE IF EXISTS lab_undo INCLUDING CONTENTS AND DATAFILES;
CREATE SMALLFILE UNDO TABLESPACE lab_undo DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_undo01.dbf' SIZE 1M REUSE AUTOEXTEND OFF;
OUTPUT
System altered.


Tablespace dropped.


Tablespace created.
Setuprun as lab_a
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, amount NUMBER, note VARCHAR2(500));
INSERT INTO orders SELECT level, 100, RPAD('x', 500, 'x') FROM dual CONNECT BY level <= 20000;
COMMIT;
GRANT SELECT, UPDATE ON orders TO lab_b;
OUTPUT
Table created.


20000 rows created.


Commit complete.


Grant succeeded.
Setuprun as lab_b
CREATE SYNONYM orders FOR lab_a.orders;
OUTPUT
Synonym created.
Setuprun as system
ALTER SYSTEM SET undo_tablespace = lab_undo;
OUTPUT
System altered.
Reproduce2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a⏳ Still running…
SET SERVEROUTPUT ON
DECLARE
  CURSOR c IS SELECT order_id, amount FROM orders;
  r c%ROWTYPE;
  total NUMBER := 0;
BEGIN
  OPEN c;
  FETCH c INTO r;
  DBMS_SESSION.SLEEP(10);  -- stands in for a slow report: read 1 row, then the rest 10 s later
  LOOP
    total := total + r.amount;
    FETCH c INTO r;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
  DBMS_OUTPUT.PUT_LINE('total = ' || total);
END;
/
Session Brun as lab_b
BEGIN
  FOR i IN 1..20000 LOOP
    UPDATE orders SET amount = amount + 1 WHERE order_id = i;
    COMMIT;
  END LOOP;
END;
/
OUTPUT
PL/SQL procedure successfully completed.
Session A: the long-running statement finishesrun as lab_a
OUTPUT
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14_1869024274$" too small
ORA-06512: at line 11
Help: https://docs.oracle.com/error-help/db/ora-01555/
Checkrun as system
SELECT name, SUBSTR(value, 1, 20) AS value FROM v$parameter WHERE name IN ('undo_tablespace', 'undo_retention') ORDER BY name;
SELECT tablespace_name, bytes/1024/1024 AS size_mb, autoextensible FROM dba_data_files WHERE tablespace_name = 'LAB_UNDO';
OUTPUT
NAME                           VALUE
------------------------------ --------------------------------------------------------------------------------
undo_retention                 900
undo_tablespace                LAB_UNDO

2 rows selected.


TABLESPACE_NAME                   SIZE_MB AUT
------------------------------ ---------- ---
LAB_UNDO                                1 NO

1 row selected.
Fixrun as system
ALTER DATABASE DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_undo01.dbf' RESIZE 100M;
OUTPUT
Database altered.

undo_retention is only a target. The undo tablespace must be big enough, or autoextend, to actually hold that much undo.

After the fix2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a⏳ Still running…
SET SERVEROUTPUT ON
DECLARE
  CURSOR c IS SELECT order_id, amount FROM orders;
  r c%ROWTYPE;
  total NUMBER := 0;
BEGIN
  OPEN c;
  FETCH c INTO r;
  DBMS_SESSION.SLEEP(10);  -- stands in for a slow report: read 1 row, then the rest 10 s later
  LOOP
    total := total + r.amount;
    FETCH c INTO r;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
  DBMS_OUTPUT.PUT_LINE('total = ' || total);
END;
/
Session Brun as lab_b
BEGIN
  FOR i IN 1..20000 LOOP
    UPDATE orders SET amount = amount + 1 WHERE order_id = i;
    COMMIT;
  END LOOP;
END;
/
OUTPUT
PL/SQL procedure successfully completed.
Session A: the long-running statement finishesrun as lab_a
OUTPUT
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14_1869024274$" too small
ORA-06512: at line 11
Help: https://docs.oracle.com/error-help/db/ora-01555/

FAQ

What does ORA-01555 snapshot too old mean?

The query needed an older version of some rows to keep a consistent view, but that undo had already been overwritten by other sessions' changes.

Why does my SELECT get ORA-01555 when it changes nothing?

The SELECT itself needs undo for rows others changed after it started. In the lab, a read-only cursor failed while another session committed updates.

How do I check the undo tablespace size and undo_retention?

SELECT name, value FROM v$parameter WHERE name IN ('undo_tablespace','undo_retention'); then SELECT bytes, autoextensible FROM dba_data_files WHERE tablespace_name = 'UNDO_TS';.

Does increasing undo_retention alone fix ORA-01555?

Not if the undo tablespace is small. In the lab, retention was already 900 seconds; the error went away only after the 1 MB undo file was resized to 100 MB.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors