重庆思庄Oracle、Redhat认证学习论坛

标题: ORACLE LOB段损坏导致应用频繁报错：ORA-00600 [25027][6] [打印本页]

作者: mahan 时间: 2024-12-6 08:48
标题: ORACLE LOB段损坏导致应用频繁报错：ORA-00600 [25027][6]
本帖最后由 mahan 于 2024-12-6 14:27 编辑

应用频繁报错：ORA-00600 [25027][6]

检查报错的trc文件发现是一条INSERT语句导致

INSERT INTO MCS_CA_AUTHENTICATE_202412 .......

检查是否存在虚假索引
SQL> select do.owner,do.object_name, do.object_type,sysind.flags
   from dba_objects do, sys.ind$ sysind
   where do.object_id = sysind.obj#
   and bitand(sysind.flags,4096)=4096;

no rows selected
并没有

确认该表的用户
select owner,table_name,tablespace_name from dba_tables where table_name='MCS_CA_AUTHENTICATE_202412';

找到报错表的大对象字段(COLUMN_NAME)
select * from dba_lobs where table_name='MCS_CA_AUTHENTICATE_202412'

建立临时中间表
create table corrupt_lobs (corrupt_rowid rowid, err_num number);

找到坏块
declare
  error_25027 exception;
  error_1555 exception;
  pragma exception_init (error_1555,-1555);
  pragma exception_init (error_25027,-25027);

  n number;
begin
  for cursor_lob in (select rowid r,JSON_DATA from LUNA_MCS_CQERTONG.MCS_CA_AUTHENTICATE_202412) loop
   begin
      n:=dbms_lob.instr(cursor_lob.JSON_DATA,hextoraw('889911'));
      exception
      when error_1555 then
         insert into corrupt_lobs values (cursor_lob.r, 1555);
         commit;
      when error_25027 then
         insert into corrupt_lobs values (cursor_lob.r, 25027);
         commit;

   end;
end loop;
end;

查询临时表
select * from corrupt_lobs

将坏块置空
update LUNA_MCS_CQERTONG.MCS_CA_AUTHENTICATE_202412 set JSON_DATA=empty_clob()
where rowid in (select corrupt_rowid from corrupt_lobs)

检查索引状态并重建索引
select index_name,status from dba_indexes where table_name='MCS_CA_AUTHENTICATE_202412'

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)