APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and laterInformation 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:
and sometimes followed by ORA-22924 error.
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"
1.b) Find the column names containing LOB data. You can DESCRIBE the table encountering the error and note down the columns names with datatype CLOB and BLOB.
SQL> DESC LOBDATA
Name Null? Type
---------- --------- ------------
ID NOT NULL NUMBER
DOCUMENT BLOB
1.c) Execute the following PL/SQL block to identify the corrupted rows. Ensure to replace <lob Column> and <Table name> with the respective LOB column and table name.
error_1578 exception;
error_1555 exception;
error_22922 exception;
pragma exception_init(error_1578,-1578);
pragma exception_init(error_1555,-1555);
pragma exception_init(error_22922,-22922);
num number;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner..&table_with_lob) loop
begin
num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupt_lobs values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupt_lobs values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupt_lobs values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;
/
After running the above procedure, it prompts for:
Enter value for table_owner : SCOTT
Enter value for table_with_LOB: EMP
1.d) After executing the above procedure, the table “CORRUPTED_LOB_DATA” will contain the rowids of the corrupted rows.
select * from corrupted_lob_data;
1.e ) If you have multiple LOB columns in the same table or want to check multiple tables, please execute the above steps again.
If there are no corrupted rows found, please skip the next step and go to Step 4.
2) Once the corruption is identified, we need to get rid of it to resolve the ORA-1555 error. Its difficult to identify the cause of the corruption unless we have the steps to reproduce the corruption at will. So, the next steps will be to salvage the data and resolve ORA-1555 error.
2.a) Restore and recover the LOB segment using physical backup.
OR
2.b) Empty the affected LOBs using the UPDATE statement
set <lob column> = empty_blob()
where rowid in (select corrupted_rowid from corrupted_lob_data);
commit;
Eg:
update LOBDATA set document = empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);
OR
2.c) Export the table without the corrupted row, like:
Example using DataPump export:
Example using conventional export:
This step is helpful to resolve the ORA-1555 error during Export or while taking backup.
OR
2.d) Delete the corrupted rows and then get the data from any other source (like standby database, flat file, or other databases etc) , if its available.
3). After resolving the corruption, upgrade your database to the latest version (or atleast above 10.2.0.4) as there are some bugs reported in the older version, which cause LOB corruption.
If corruption is resolved, you can skip Step 4.
4) If there is no corrupted rows found, then the solution is to ensure the read consistent images are retained in the LOB segment for adequate duration. You can increase either the RETENTION or the PCTVERSION attribute of the LOB column
4.a) Till 11gR1, the RETENTION attribute of the LOB segment will be equal to the UNDO_RETENTION parameter. Hence we recommend to set UNDO_RETENTION to the maximum of the duration of the queries in the database. Check the maxquerylen:
and set this value for UNDO_RETENTION parameter
After increasing the value of UNDO_RETENTION parameter, ensure to add space to the UNDO tablespace. A higher value for UNDO_RETENTION means the undo records are retained for longer period (on both LOB segment and the UNDO tablespace) and hence increased space utilization in the UNDO tablespace.
4.b) From 11gR2 and above, we can set the retention for the LOB column to a value other than the UNDO_RETENTION parameter:
Verify the same:
OR
3.c) Increase the PCTVERSION attribute of the LOB segment
Higher values of PCTVERSION will ensure the more space is allocated for the old versions of LOB data.
For more details refer to the troubleshooting guide:
Troubleshooting ORA-01555 - Snapshot Too Old: Rollback Segment Number "String" With Name "String" Too Small(Doc ID 1580790.1)
Troubleshooting ORA-01555/ORA-01628/ORA-30036 During Export and Import(Doc ID 1579437.1)
Related Issues:
If the ORA-1555 is reported with Query Duration = 0 or a few seconds like Query Duration = 10 , refer to Doc ID 1950577.1
If the ORA-1555 error message reports an unrealistic high Query Duration (which is far higher than the actual query duration), refer to Doc ID 1950961.1
For ORA-1555 errors reported on Export, refer to documents Doc ID 1950937.1 and Doc ID 1950978.1. They explain the solution with and without LOB data, respectively.
In other cases where the ORA-1555 is reported refer to the document Doc ID 1950897.1