数据库lob字段表坏块报ORA-22924 ORA-01555处理步骤

1.界面报错 ora-22924 ora-01555报错

2.开启数据库跟踪：

su  -  oracle 

sqlplus / as sysdba
alter system set events '600 trace name errorstack forever';

alter system set events '1555 trace name errorstack forever';

alter system set events '22924 trace name errorstack forever';

3.界面重现问题，可以根据数据库警告日志和trc文件定位到有问题的表

4.查看表属于哪个用户，desc 查看表结构，是否存在lob字段

5.构造相关的坏块表corrupt_lobs

6.执行存储过程

create table corrupt_lobs (corrupt_rowid rowid, err_num number);

set serveroutput on

declare

  error_1578 exception;

  error_1555 exception;

  error_22922 exception;

 error_22924 exception;

  pragma exception_init(error_1578,-1578);

  pragma exception_init(error_1555,-1555);

  pragma exception_init(error_22922,-22922);

  pragma exception_init(error_22924,-22924);

  n number;

begin

  for cursor_lob in (select rowid r, FSETTING from  TEST01.T_GL_USERSETTING) loop

  begin

    n:=dbms_lob.instr(cursor_lob.FSETTING,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;

    when error_22924 then

      insert into corrupt_lobs values (cursor_lob.r, 22924);

      commit;

    end;

  end loop;

end;

/

说明：TEST01.T_GL_USERSETTING是有问题的用户名和表名，

FSETTING是那个表的lob字段名称

7.检查构造的表是否有数据，如果有数据的话，证明有坏块

select  count(*)  from  corrupt_lobs;

8.有问题的置空

update  TEST01.T_GL_USERSETTING  set FSETTING = empty_blob()     where rowid in (select corrupt_rowid from corrupt_lobs);

commit;

说明：清空clob值（若表类型为blob，使用empty_blob() 否则为(empty_clob() ) )

9.关闭跟踪

su - oracle 

sqlplus / as sysdba

alter system set events '600 trace name errorstack off';

alter system set events '1555 trace name errorstack off';

alter system set events '22924 trace name errorstack off';