Oracle数据库 expdp导出表报错ORA-01555处理

–expdp导出某一张表的时候总是出现如下报错

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的使用情况,发现并未占用多少undo,undo空间其实是够用的
随后查看表的字段,发现有lob字段,很有可能是lob字段损坏导致的

–创建一张临时表,保存损坏的表记录信息

create table corrupt_lobs_wfcaserun (corrupt_rowid rowid);

–执行下面的存储过程(LOB字段写导出表的lob字段,tablename写表名)

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);
  num number;
begin
  for cursor_lob in (select rowid r, LOB字段 from tablename) loop
    begin
      num := dbms_lob.instr(cursor_lob.LOB字段, hextoraw('889911'));
    exception
      when error_1578 then
        insert into corrupt_lobs_wfcaserun values (cursor_lob.r);
        commit;
      when error_1555 then
        insert into corrupt_lobs_wfcaserun values (cursor_lob.r);
        commit;
      when error_22922 then
        insert into corrupt_lobs_wfcaserun values (cursor_lob.r);
        commit;
    end;
  end loop;
end;
/

–如果有损坏的记录,会自动记录到刚刚创建的临时表当中

select * from corrupt_lobs_wfcaserun;

–表里记录了损坏字段的rowid,根据rowid对损坏的记录进行删除或者是更新

delete from tablename where rowid ='';
update tablename set lob字段='' where rowid ='';

–损坏的记录处理后再重新进行exppd导出,终于正常

posted @ 2024-10-15 14:20  学无止境的小一  阅读(50)  评论(0编辑  收藏  举报  来源