Sunday, February 3, 2008

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

If the ORA-01555 error occured along with the ora-22924 error, then the issue is not related with the undo segment but with the LOB segment which is being overwritten.

22924, 00000, "snapshot too old"
Cause: The version of the LOB value needed for the consistent read was already overwritten by another writer.

Action: Use a larger version pool.

To fix:
-------
ALTER TABLE tablename MODIFY LOB (lobname) (PCTVERSION 100);
If the pctversion is set 100, then lob images will not get overwritten.

Looks like this happens with many concurrent updates. Snapshots and long running queries need to use an old version of a LOB - that version gets overwritten as PCTVERSION is too small.

No comments: