两种原因:
1.undo表空间太小,undo保留时间太短
2.读取的数据有坏块
解决方案:
1.第一种问题,加大undo表空间及undo保留时间
2.第二种解决坏块的问题:
(1)首选创建一个处理坏块的表
create table corrupt_lobs (corrupt_rowid rowid, err_num number);
(2)执行如下程序
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, &&lob_column from &table_owner.&table_with_lob) loop
begin
num := dbms_lob.instr (cursor_lob.&&lob_column, 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;
/
Enter value for lob_column : lob字段名
Enter value for table_owner : 所属用户
Enter value for table_with_LOB: lob字段所在的表
根据查询出来的坏块,将坏块更新为空:
update <owner>.<tablename>
set <lob column> = empty_blob()
where rowid in (select corrupted_rowid from corrupted_lob_data);
commit;
备注:如有多个字段重复执行上面的程序代码