Please Login or Register
Advanced search

196 - ORA-01555: snapshot too old

Submitted by admin on Fri, 11/04/2011 - 15:21

ORA-01555 is caused by many reason. One of them is "fetch across commit" in PL/SQL sometimes.
Generally you should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 errors.
The higher frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions,
then it causes ORA-1555 errors.

To fix this problem, easy to rewrite code like this:

FOR records IN cursor_A LOOP
   ......
   COMMIT;
END LOOP;
COMMIT;

You should change this to :

FOR records IN my_cursor LOOP
   ......
   cnt := cnt+1;
   IF mod(cnt, 1000) = 0 THEN    -- Commit every 1000 records
      COMMIT;
   END IF;
END LOOP;
COMMIT;

You can get an ORA-01555 error with a too-small undo_retention, even with a large undo tables.  However, you can set a super-high value
for undo_retention and still get an ORA-01555 error.  Also see these important notes on commit frequency and the ORA-01555 error

ORA-01555: snapshot too old: rollback segment number 256 with name "_SYSSMU256_1302547324$" too small
The ORA-01555 snapshot too old error can be addressed by several remedies:

1. Re-schedule long-running queries when the system has less DML load.

2. Increasing the size of your rollback segment (undo) size.
   The ORA-01555 snapshot too old also relates to your setting for automatic undo retention.

3. Don't fetch between commits.(Need to chenge the code)