Home / Databases / Oracle

DatabasesTroubleshootingIntermediate

Oracle ORA-01555 snapshot too old: finding the long query, sizing undo and stopping it coming back

Long reports or exports fail with ORA-01555. Read the alert log, check V$UNDOSTAT, size UNDO_RETENTION and the undo tablespace, and fix fetch-across-commit and LOB cases.

Deepak Bhardwaj5 min read

Applies toOracle Database 12c, 19c, 21c and 23ai with automatic undo management

Symptoms

  • A long report, batch job or Data Pump export fails with:
ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_..." too small
  • The job worked last month and fails now that the data has grown or another batch runs at the same time.
  • Retrying at night usually works.

Why it happens

Oracle gives every query a read-consistent view of the data as of the moment it started. If a row changed after that, Oracle rebuilds the older version from undo. If that undo has already been overwritten, the query can't reconstruct the old version and fails with ORA-01555.

So the error means: a query ran longer than the undo it needed was kept. Two levers fix it: make the query shorter, or keep undo longer.

Step 1: read the alert log

Oracle writes the failing statement and its duration to the alert log:

ORA-01555 caused by SQL statement below (SQL ID: 7h35uxf5uhmm1, Query Duration=10823 sec, SCN: 0x0000.1a2b3c4d):
SELECT ...

Find it with ADRCI or the V$DIAG_ALERT_EXT view:

SELECT originating_timestamp, message_text
FROM   v$diag_alert_ext
WHERE  message_text LIKE '%ORA-01555%'
AND    originating_timestamp > SYSTIMESTAMP - INTERVAL '7' DAY
ORDER  BY originating_timestamp DESC;

Note the query duration and SQL ID. That number is the retention you'd need.

Step 2: compare with what undo actually keeps

SHOW PARAMETER undo

SELECT tablespace_name, retention
FROM   dba_tablespaces
WHERE  contents = 'UNDO';

V$UNDOSTAT has a row per 10-minute interval for roughly the last four days:

SELECT begin_time, end_time,
       tuned_undoretention,   -- seconds Oracle is actually keeping undo
       maxquerylen,           -- longest query in the interval (seconds)
       maxqueryid,
       ssolderrcnt,           -- ORA-01555 count
       nospaceerrcnt,         -- out-of-space-in-undo count
       unxpstealcnt           -- unexpired undo stolen
FROM   v$undostat
WHERE  begin_time > SYSDATE - 1
ORDER  BY begin_time;

How to read it:

Pattern Meaning
tuned_undoretention < failing query duration Undo not kept long enough
unxpstealcnt > 0 around the failure Undo tablespace too small; Oracle stole unexpired undo to make space
tuned_undoretention far above undo_retention Autoextend lets Oracle keep more; the problem is space, not the parameter
maxquerylen steadily growing week by week Query or data growth; fix the query too

Check undo tablespace size and autoextend:

SELECT file_name, bytes/1024/1024 AS mb, maxbytes/1024/1024 AS max_mb, autoextensible
FROM   dba_data_files
WHERE  tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');

Step 3: fix it

Keep undo longer

Set UNDO_RETENTION above the longest legitimate query, with margin:

ALTER SYSTEM SET undo_retention = 14400 SCOPE = BOTH;   -- 4 hours

Give the undo tablespace room to honour it:

ALTER DATABASE DATAFILE '/u02/oradata/PROD/undotbs01.dbf' AUTOEXTEND ON MAXSIZE 64G;
-- or add a datafile
ALTER TABLESPACE undotbs1 ADD DATAFILE '/u02/oradata/PROD/undotbs02.dbf' SIZE 8G AUTOEXTEND ON MAXSIZE 32G;

Oracle Advisor can estimate the size needed for a target retention:

SELECT dbms_undo_adv.required_undo_size(14400) AS required_mb FROM dual;

Retention guarantee (use with care)

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Warning

With RETENTION GUARANTEE, Oracle never overwrites unexpired undo. If the undo tablespace fills, DML fails with out-of-space errors instead of a report failing with ORA-01555. Only use it with plenty of undo space and monitoring, and switch back with RETENTION NOGUARANTEE if DML starts failing.

Make the query shorter

  • Tune the SQL from the alert log (SQL ID) with its execution plan; a full scan that became a 3-hour query is the root cause, not undo.
  • Run reports on an Active Data Guard standby, or from a snapshot or clone, instead of on the busy primary.
  • Schedule the long job away from the heavy-DML batch that churns undo.

Fetch across commit

A classic self-inflicted case: a PL/SQL loop opens a cursor on a table, updates the same table and commits every N rows. Each commit lets the undo the cursor needs be reused.

-- Anti-pattern
FOR r IN (SELECT id FROM orders WHERE status = 'NEW') LOOP
  UPDATE orders SET status = 'DONE' WHERE id = r.id;
  IF MOD(c, 1000) = 0 THEN COMMIT; END IF;
END LOOP;

Fix it with set-based DML, a single commit at the end, or by processing in independent batches that reopen the cursor.

LOB columns

ORA-01555 on LOBs (often with ORA-22924) uses LOB retention, not the undo tablespace. For BasicFile LOBs check PCTVERSION / RETENTION; for SecureFiles check the LOB's RETENTION setting:

SELECT table_name, column_name, securefile, pctversion, retention_type
FROM   dba_lobs
WHERE  owner = 'APP';

Prevent it next time

  • Alert on ssolderrcnt > 0 and unxpstealcnt > 0 from V$UNDOSTAT.
  • Track the longest report and export durations; keep UNDO_RETENTION at least 25% above them.
  • Size undo for peak DML plus retention, with autoextend and a sensible MAXSIZE.
  • Run Data Pump exports with FLASHBACK_TIME=SYSTIMESTAMP only when undo covers the whole export, or export from a standby or clone.

Related fixes