Oracle exp导出时出现ORA-01555和ORA-22924的解决-优快云博客

Oracle exp导出带有LOB字段表时出现ORA-01555和ORA-22924报错，给出解决方法。[@more@]

1、问题描述

环境为aix5.3系统，oracle9.2.0.1的库，库内rhxt用户有一rk_zp表,这张表zp字段是blob型的，这张表43GB,每次exp至几个G时就报ORA-01555和ORA-22924错了。

2、问题解决

通过实际的测试及查MOS，发现这是因为blob某些行有问题触发了oracle BUG
官方给的解决方式如下：

<1>建一张临时表存储所有corrupted LOBs行的rowid.

SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);

<2>运行如下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, zp from rkxt.rk_zp) loop
begin
n:=dbms_lob.instr(cursor_lob.zp,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;
/