Wednesday, September 16, 2009

ORA-01555: snapshot too old: rollback segment number with name "" too small

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

If you're using manual UNDO Mode or use an Oracle version <9i,>

select rb.segment_name, rs.optsize, rs.hwmsize
from v$rollstat rs, dba_rollback_segs rb
where rs.usn = rb.segment_id

To change the optimal size you can issue following select:

alter rollback segment rbs0 storage (optimal 100m);
Pay attention that the size you specify for optimal will be constantly allocated inside the tablepace.
If you are using automatic UNDO mode, you can try to increase the value of undo_retention.

alter system set undo_retention=10800;

The value of undo_retention is the time in seconds the database tries to keep the undo blocks (not mark as being free) inside the undo tablespace.

No comments: