ORA-1555 failures ("snapshot too old") Issue/Fixes/WorkAround

If you get ORA-1555 Failures or SnapShot Too Old errors

Solution:

1. Increase the size of undo tablespace or
2. Increase the undo_retentoin time

Steps to Fix the issue

1. First you find out what is the undo tablespace is needed for your database
2. Second you find out what is the undo retentino time is needed for your database

---Using following Scripts -----

Script #1

select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))
"OPTIMAL UNDO RETENTION (Secs)"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size'

/

Script #2

select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)",
substr(e.value,1,25) "UNDO RETENTION (Secs)",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "
NEEDED UNDO SIZE (MEGS)"
from (select sum(a.bytes) undo_size
from v$datafile a, v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
from v$undostat) g
where e.name = 'undo_retention' and f.name = 'db_block_size'
/

You will get Ideal Undo_tablespace size and Undo_retention time. According that outputs, Set your values as follows

If it is just problem with undo_retention, then do as follows

1. Update init.ora with the following undo_retention=36600
2. Bounce the database

If it is just problem with undo_tablespace size, then do as follows

Solution in this situation is to create a NEW UNDO tablespace with smaller size and switch over to your current UNDO tablespace to NEW UNDO tablespace.

Example:
/* Create new undo tablespace with smaller size
*/SQL> create undo tablespace undotbs2 datafile ‘/u01/oradata/decipher/undotbs2_01.dbf’ size 1024m autoextend on next 256m maxsize 10240m;
/* Set new tablespace as undo_tablespace */SQL> alter system set undo_tablespace= UNDOTBS2 scope=both;
If the parameter value for UNDO TABLESPACE is set to ” (two single quotes), the current undo tablespace will be switched out without switching in any other undo tablespace. This is normally to un-assign an undo tablespace in the event that you want to revert to manual undo management mode.
/* Drop the old tablespace */SQL> drop tablespace UNDOTBS1 including contents;