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导出,终于正常