ORA-00600问题查看

1、前提：

MES查询报错:

2、执行语句：

select ID,
CMSGID MSGID,
CBANHAO BANHAO,
0 IsChecked,
CXUHAO XUHAO,
CSOURCETYPE BIZ_TYPE,
REMARKS BIZ_DESC,
ISTATUS,
CACTION,
IABANDONTYPE,
INUM,
INUM2,
CFREE9 GONGXU,
(select count(0)
from mes_prd.NEIP_Task
where CSOURCETYPE in
(select distinct CCODE
from mes_prd.NEIP_Relationship
start with CFACODE = a.CSOURCETYPE
connect by nocycle prior CCODE = CFACODE)
and CBANHAO = a.cbanhao
and (instr(cxuhao, a.CXUHAO) > 0 or instr(a.CXUHAO, cxuhao) > 0)
and dcreatedate >= a.dcreatedate
and id <> a.id) TASKNUM,
case
when CACTION = 'INSERT' then
'创建'
when CACTION = 'UPDATE' then
'修改'
when CACTION = 'DELETE' then
'删除'
when CACTION = 'PAUSE' then
'停制'
when CACTION = 'CONTINUE' then
'开制'
when CACTION = 'FEEDBACK' then
'反馈'
when CACTION = 'CLOSE' then
'关闭'
else
''
end ACTIONNAME,
case
when ISTATUS = 0 then
'未发送'
when ISTATUS = 1 then
'发送成功待反馈'
when ISTATUS = 2 then
'反馈成功'
when ISTATUS = 3 then
'反馈失败'
when ISTATUS = 4 then
'转发处理中'
when ISTATUS = 9 then
'已关闭'
when ISTATUS = -1 then
'发送失败'
else
''
end STATUSNAME,
case
when IABANDONTYPE = 1 then
'直接放弃'
when IABANDONTYPE = 2 then
'随前续任务放弃'
else
''
end ABANDONTYPE,
TO_CHAR(DABANDONDATE, 'YYYY-MM-DD HH24:MI:SS') AS ABANDON_TIME,
TO_CHAR(DOVERDATE, 'YYYY-MM-DD HH24:MI:SS') AS FINISH_TIME,
TO_CHAR(DCREATEDATE, 'YYYY-MM-DD HH24:MI:SS') AS CREATEDATE
from mes_prd.NEIP_TASK A
WHERE 1 = 1
and DCREATEDATE >=
TO_DATE('2022-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and DCREATEDATE <
TO_DATE('2022-10-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
order by id desc

3、执行上述语句，报错：

4、跟踪trace

19:34:29 SYS@mesorcl>oradebug setmypid
Statement processed.
19:34:38 SYS@mesorcl>oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/mesorcl/mesorcl/trace/mesorcl_ora_4753.trc
19:35:12 SYS@mesorcl>alter session set events '10046 trace name context forever,level 12';

5、查看trc文件

执行2中语句，并查看文件

是10号文件的2318513块的第三号槽位找不到了；

6、根据文件号和块号，定位到表

Select owner, segment_name, segment_type, partition_name,tablespace_name 
From dba_extents 
Where relative_fno = 10
And 2318513 between block_id and (block_id+blocks-1);

 OWNER   SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME
 MES_PRD  NEIP_TASK      TABLE                       MES_PRD

表是neip_task表

7、bbed查看块（但是报错）

8、查看块所在表

Select owner, segment_name, segment_type, partition_name,tablespace_name 
From dba_extents 
Where relative_fno = 10
And 2318513 between block_id and (block_id+blocks-1);

9、查看百度分析，可能为索引问题

（1）在进行rman恢复时，没有进行完全恢复，也没有进行归档恢复，并利用隐藏参数进行强制启动数据库。

（2）查看表NEIP_TASK对应索引

select owner,index_name,index_type,table_name from dba_indexes where table_owner='MES_PRD' and table_name='NEIP_TASK';

OWNER        INDEX_NAME      INDEX_TYPE TABLE_NAME
MES_PRD     PK_NEIP_TASK_ID    NORMAL    NEIP_TASK

（3）分析索引

analyze index mes_prd.PK_NEIP_TASK_IDvalidate structure online;

仍然报错：ORA-00600

（4）查看索引创建语句

set long 9999
select dbms_metadata.get_ddl('INDEX','PK_NEIP_TASK_ID','MES_PRD') from dual;

（4）删除索引

drop index PK_NEIP_TASK_IDvalidate ;（因为是主键约束索引，这种方式会报错，-报错，ORA-02429: 无法删除用于强制唯一/主键的索引，所以使用约束进行删除）

ALTER TABLE NEIP_TASK DROP CONSTRAINT PK_NEIP_TASK_ID

（5）重建索引

--重建索引还是报错：00600，

SQL> CREATE UNIQUE INDEX "MES_PRD"."PK_NEIP_TASK_ID" ON "MES_PRD"."NEIP_TASK" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MES_PRD"
/

--重建约束可以临时解决。

SQL> alter table NEIP_TASK add constraint PK_NEIP_TASK_ID primary key(id);