ORA-01555:Snapshot too old rollback segment number

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 the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

SELECT SUM(a.bytes) as 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#;

Then find the number of undo block per second;

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) as UNDO_BLOCK_PER_SECOND
  FROM v$undostat;

Let’s find the database block size;

SELECT TO_NUMBER(value) as DB_BLOCK_SIZE
 FROM v$parameter
WHERE name = 'db_block_size';

Finally, we solve them all with a single query to find optimum UNDO_RETENTION period;

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      as NEEDED_UNDO_SIZE"
  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';

-- ALTER SYSTEM SET UNDO_RETENTION = OPTIMUM SIZE;

Bonus

  select '"ORA-01555 (Snapshot too old)" Error : '
  || sum(ssolderrcnt)
  from v$undostat;