Oracle Error ORA-01555: snapshot too old
-- When UNDO parameter is too small and query is operating very long, this error is 
-- thrown: "ORA-01555: snapshot too old: rollback segment number .... too small"

-- The show parameter undo will display the current setup


SHOW PARAMETER undo;

NAME              TYPE    VALUE
----------------- ------- -------
temp_undo_enabled boolean FALSE
undo_managememt   string  AUTO
undo_retention    integer 86400
undo_tablespace   string  UNDO_T1


-- The above is sample output
-- Tablespace retension value has to be analyzed 


SELECT tablespace_name, retiontion FROM dba_tablespaces;

SELECT MAX(maxquerylen) max_ql FROM v$undostat;

SELECT MAX(maxquerylen)/60/60 max_ql, 
 86400/60/60 retention_val         --undo_retention value from SHOW PARAMETER undo
FROM v$undostat;

Tablespace Reference

Errors and Resolution

Oracle registered trademark of Oracle Corporation.

Last Revised On: May 31st, 2017

  1153