记录一次LOB损坏导致的ORA-01555和ORA-22924关于snapshot too old报错

expdp导库的时候，日志提示：

ORA-31693: Table data object "SXWTSPJ"."UNE_CBILL_CLOUD" failed to load/unload and is being skipped due to error:

ORA-02354: error in exporting/importing data

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

ORA-22924: snapshot too old

遇到ORA-01555报错最直接的反应就是undo表空间大小是否足够，undo_retention参数是否设置太小，

如果是这种情况，解决方法为：

1.alter table table_name modify lob(column) (pctversion 80);

2.alter system set undo_retention=180000;

经过验证，均不是以上问题造成的.

由于该表有BLOB和CLOB类型的列，怀疑是BLOB有损坏，解决方法如下：

1.创建表存放lob损坏行的rowid

create table corrupted_lob_data_cbill_cloud (corrupt_rowid rowid, err_num number);

2.执行如下plsql块，找出存在损坏lob的行

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,FINVOICEINGPARTYSIGNATURE from une_cbill_cloud) loop
    begin
      n := dbms_lob.instr (cursor_lob.FINVOICEINGPARTYSIGNATURE, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupted_lob_data_cbill_cloud values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupted_lob_data_cbill_cloud values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupted_lob_data_cbill_cloud values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;

3.查询结果查看哪些行 blob列有损坏

select * from corrupted_lob_data_cbill_cloud;

4.将损坏的blob列置空

update table_name set column = EMPTY_BLOB() where rowid in (select corrupt_rowid from corrupted_lob_data_cbill_cloud);

至此问题成功解决。