ORA-01555: snapshot too old

ORA-01555:

snapshot too old: rollback segment number string with name "string" too small

Cause:

Rollback records needed by a reader for consistent read are overwritten by other writers

Action:

If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

Solution 1: Increase UNDO TABLESPACE and retry

Solution 2:Increase undo_retention setting

show parameter undo_management

ALTER SYSTEM SET UNDO_RETENTION = 2400;
alter system set undo_retention=2400 scope=both


-- Guarantee the minimum threshold is maintained.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;


SELECT tablespace_name, retention FROM dba_tablespaces;


TABLESPACE_NAME RETENTION
-----------
SYSTEM NOT APPLY
UNDOTBS1 GUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY




---------------------------------------------------------------------------------------------------------------



SQL> select max(maxquerylen) from v$undostat;


MAX(MAXQUERYLEN)
62057


SQL> show parameter undo


NAME TYPE
--------------------------------
VALUE
undo_management string
AUTO
undo_retention integer
25000





SQL> select (62057/60)/60 query,(25000/60)/60 retention from dual



     QUERY        RETENTION
----------           ----------
17.2380556     6.94444444







In above example query exectuing tenure is 17 hours while undo retention is approximate 7 hours,so change retention period to 20% extra with  query execution tenure and then check the query again.


At least set undo retention to 75000