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;

/

<3>用如下语句把相应行的blob字段清空:

SQL> update LOBDATA set document = empty_blob()

where rowid in (select corrupt_rowid from corrupt_lobs);

<4>重新执行exp操作