APPLIES TO:
Oracle Database – Enterprise Edition – Version 9.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
ORA-1555 or snapshot too old error reported on a LOB segment. The error message looks like:
ORA-01555: snapshot too old: rollback segment number with name “” too small
and sometimes followed by ORA-22924 error.
ORA-01555: snapshot too old: rollback segment number with name “” too small
ORA-22924: snapshot too old
CAUSE
LOB data doesn’t use the Undo segment for retaining the read consistent images. The old versions are stored in the LOB Segments itself, before any DMLs.
The ORA-01555 on a LOB segment is reported generally in two cases:
a) The query is accessing a LOB segment which is corrupted
OR
b) Read consistent images of the LOB data is not available in the LOB Segment. This happens due to the wrong setting of PCTVERSION / RETENTION attributes of the LOB segment.
SOLUTION
1) The first step to resolve ORA-1555 on LOB column is to check for corruption. This is the most common case.
1.a) Create a dummy table for storing all rowids of the corrupted LOBs. Let’s call it “corrupt_lobs”
SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);
1.b) Find the column names containing LOB data. You ca