EXPDP ORA-02354 error in exporting/importing data ORA-01555: snapshot too old

Check lob status

Cause
The cause of these errors could be a LOB corruption. Maintaining the LOBs will solve the problem with Exp/Expdp.

Copyright (c) 2021, Oracle. All rights reserved. Oracle Confidential.
Click to add to Favorites ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. (Doc ID 452341.1) To BottomTo Bottom

In this Document
Symptoms
Cause
Solution
References

Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database 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.
Checked for relevance on 29-Jan-2014
Symptoms

When attempting to export tables that contain lobs, the following errors occur:

• using DataPump export, EXPDP:
  Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
  ORA-31693: Table data object "OWNER"."TABLEABC" failed to load/unload and is being
  skipped due to error:
  ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
  ORA-01555: snapshot too old: rollback segment number with name "" too small
  ORA-22924: snapshot too old

• using original export, EXP:
  . . exporting table TABLEABC
  EXP-00056: ORACLE error 1555 encountered
  ORA-01555: snapshot too old: rollback segment number with name "" too small
  ORA-22924: snapshot too old
  Cause

The cause of these errors could be a LOB corruption. Maintaining the LOBs will solve the problem with Exp/Expdp.
Solution
The script below can always be used to detect the lob corruption

1. Run the following script against the LOB tables in order to check for corruption:
   set serverout on
   exec dbms_output.enable(100000);
   declare
   page number;
   len number;
   c varchar2(10);
   charpp number := 8132/2;

begin
for r in (select rowid rid, dbms_lob.getlength (<your_clob_column>) len
from <your_table_with_clcob_column>) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp))
into c
from <your_table_with_clcob_column>
where rowid = r.rid;

    exception
      when others then
        dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
        dbms_output.put_line (sqlerrm);
    end;
  end loop;
end if;

end loop;
end;
/

If no error, LOB is OK.

Else if error (i.e. ORA-1403, ORA-1578, ORA-1555, ORA-22922) happens that means LOB has been corrupted.

Solution

-- 1. Create a new temporary 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);

-- 2. Make a desc on the table containing the LOB column:

DESC <TABLE_NAME>

Name Null? Type

---

NOT NULL NUMBER
<LOB_COLUMN> BLOB

-- Run the following PLSQL block:

declare
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);
n number;
begin
for cursor_lob in (select rowid r, <LOB_COLUMN> from <TABLE_NAME>) loop
begin
n:=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;
/

-- In the end all rowids of the corrupted LOBs will be inserted into the corrupt_lobs newly created table.

-- A possible solution would then be to empty the affected LOBs using a statement like:

SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob()
where rowid in (select corrupt_rowid from corrupt_lobs);

( for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB )

-- Or export the table without the corrupted row, like:

% expdp system/ DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY="WHERE rowid NOT IN ('<corrupt_rowid>')"