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