ORA-01555: snapshot too old: rollback segment too small
ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20_4249263262$" too smallA 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
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
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;System altered.
Tablespace dropped.
Tablespace created.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;Table created.
20000 rows created.
Commit complete.
Grant succeeded.CREATE SYNONYM orders FOR lab_a.orders;Synonym created.ALTER SYSTEM SET undo_tablespace = lab_undo;System altered.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;
/BEGIN
FOR i IN 1..20000 LOOP
UPDATE orders SET amount = amount + 1 WHERE order_id = i;
COMMIT;
END LOOP;
END;
/PL/SQL procedure successfully completed.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/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';NAME VALUE
------------------------------ --------------------------------------------------------------------------------
undo_retention 900
undo_tablespace LAB_UNDO
2 rows selected.
TABLESPACE_NAME SIZE_MB AUT
------------------------------ ---------- ---
LAB_UNDO 1 NO
1 row selected.ALTER DATABASE DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/lab_undo01.dbf' RESIZE 100M;Database altered.undo_retention is only a target. The undo tablespace must be big enough, or autoextend, to actually hold that much undo.
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;
/BEGIN
FOR i IN 1..20000 LOOP
UPDATE orders SET amount = amount + 1 WHERE order_id = i;
COMMIT;
END LOOP;
END;
/PL/SQL procedure successfully completed.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