ORA-31693 ORA-01555 ORA-22924解决方法

记一次blob字段数据损坏导致数据库备份报的处理办法

ORA-31693:表数据对象 "OA""JK JOB DETAILS”无法加载/卸载并且被跳过，错误如下: ORA-02354:导出/导入数据时出错

ORA-01555:快照过旧:回退段号 (名称为"")过小

ORA-22924:快照太旧

先尝试查看undo最大时间

select max(maxquerylen) from v$undostat;

查看系统设置

show parameter undo_r;

查看表设置

select table_name,column_name,pctversion,retention from dba_lobs where table_name='JK_JOB_DETAILS';

修改

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;

ALTER TABLE JK_JOB_DETAILS MODIFY LOB(JOB_DATA)(retention);

执行后导出数据依然报错

尝试以下办法

创建表记录rowid

create table corrupted_lob_data (corrupted_rowid rowid);

执行过程

declare

error_1555 exception;

pragma exception_init(error_1555,-1555);

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);

commit;

end;

end loop;

end;

查看损坏数据

select * from JK_JOB_DETAILS where rowid in (

select * from corrupted_lob_data ) ;

删除损坏数据

delete JK_JOB_DETAILS where rowid in (

select * from corrupted_lob_data ) ;

导出不报错。