ORA-01555 ORA-22924故障处理

用户逻辑导出有BLOB字段数据的时候，在检验图像结果这张表，报ora-01555 ora-22924 错误提示如下：

ORA-31693: Table data object "XCKY"."COMMON_PICTURE" 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

查看数据库参数undo_retention确认并非有undo导致，在执行表mvoe过程中同样提示

alter table XCKY.COMMON_PICTURE move lob(CONTENT) store as (tablespace system_info);
#错误提示
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

通过上面的错误可以得出content字段有问题，通过查询mos得到如下解决方案

sqlplus / as sysdba
alter system set events '600 trace name errorstack forever';
alter system set events '1555 trace name errorstack forever';
alter system set events '22924 trace name errorstack forever';

set concat off
create table corrupted_lob_data (corrupted_rowid rowid,err_num number); 
set concat off
declare  
  error_1555 exception;  
  pragma exception_init(error_1555,-1555); 
  error_22924 exception;  
  pragma exception_init(error_22924,-22924);  
  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,01555);  
        commit;  
      when error_22924 then
        insert into corrupted_lob_data values (cursor_lob.r,22924);  
        commit;   
    end;  
  end loop;  
end;  

执行完成后通过查询corrupted_lob_data表即可得到blob有问题的数据，可通过empty_blob()函数充值blob的值来解决该错误。

update xcky.common_picture set content= empty_blob() where rowid in (select corrupt_rowid from corrupt_lobs);
commit;
#说明：清空clob值（若表类型为blob，使用empty_blob() 否则为(empty_clob() ) )

 关闭跟踪

sqlplus / as sysdba
alter system set events '600 trace name errorstack off';
alter system set events '1555 trace name errorstack off';
alter system set events '22924 trace name errorstack off';