APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
Following errors encountered during export:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old
CHANGES
CAUSE
LOB segment corruption.
How To Confirm LOB Segment Corruption Using Export Utility
- Identify the table rowid's referencing the corrupted LOB segment blocks by running the PL/SQL procedure mentioned in the Note 787004.1.
As there is already a similar PL/SQL procedure indicated in various notes such as: Note 452341.1 or Note 253131.1, the reason for this article is to propose a method to confirm the LOB segment corruption using export utility by exporting the corrupted rowids.
For example:
If you encounter the error “ORA-1555: snapshot too old: rollback segment number with name " " too small” during export of table (<TABLE_NAME>) with a LOB column (<LOB_COLUMN>), then run the PL/SQL procedure mentioned in the Note 787004.1.
Note: Run the PL/SQL procedure after creating a temporary table “CORRUPTED_LOB_DATA”. This table is used to spool the corrupted rowids.
create table corrupted_lob_data (corrupted_rowid rowid);
set concat off
declare
error_1555 exception;
pragma exception_init(error_1555,-1555);
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_1555 then
insert into corrupted_lob_data values (cursor_lob.r);
commit;
end;
end loop;
end;
/
After running the above procedure, it prompts for:
Enter value for lob_column : <LOB_COLUMN>
Enter value for table_owner : <SCHEMA_NAME>
Enter value for table_with_LOB: <TABLE_NAME>
Like this, we can check the corruption in all the LOB columns.
In this example, the output of the table “CORRUPTED_LOB_DATA” is showing three rowid’s referencing the corrupted lob segment
select * from corrupted_lob_data;
CORRUPTED_ROWID
---------------------
AAEWBsAAGAAACewAAC
AAEWBsAAGAAACewAAF
AAEWBsAAGAAACewAAG
3 rows selected
Confirm the LOB corruption using Datapump: