--根据trace文件 oer 8102.2 - obj# 151536, rdba: 0x04b0c98b(afn 18, blk# 3197323)
select owner,object_name,subobject_name,object_id,object_type from dba_objects where object_id=151536;
--P_LOG_ID_184 这是一张分区表的分区索引，前面的值是分区名
select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_owner='OWNER' and partition_name='P_LOG_ID_184'
--184000000 找到所在分区对应的值，即存放小于184000000并且大于183000000
select max(login_id) from OWNER.LOG_RECORD_DETAIL where login_id >=183000000;
--183623608 找到最新最大的值，好做比较
--全表扫描，与索引 数据对比，明显少一条记录
SELECT /*+ FULL(t1) */ count(1)
FROM OWNER.LOG_RECORD_DETAIL t1 where login_id >=183000000 and login_id <=183623608
-- 623609
SELECT /*+ index(t LOG_RECORD_DETAIL_IX2) */ count(1)
FROM OWNER.LOG_RECORD_DETAIL t where login_id >=183000000 and login_id <=183623608;
-- 623608
--LOG_RECORD_DETAIL表的结构介绍下
以login_id为主键索引(序列值递增)，也是以login_id做分区的。
LOG_RECORD_DETAIL_IX2 是三个列的分区索引LOGOUT_TIME、GAME_ID、GATEWAY_ID
第二次遇到的时候dbv检查这个索引存在的数据文件，看结果是没有坏块
dbv file=
1>、文件1
DBVERIFY - Verification complete
Total Pages Examined : 4193792
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 4178689
Total Pages Failing (Index): 0
Total Pages Processed (Other): 15058
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 45
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3849499 (9.3849499)
2>、文件2
DBVERIFY - Verification complete
Total Pages Examined : 2818560
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2557153
Total Pages Failing (Index): 0
Total Pages Processed (Other): 13542
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 247865
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 4022558 (9.4022558)
3>、文件3
DBVERIFY - Verification complete
Total Pages Examined : 3151360
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2958776
Total Pages Failing (Index): 0
Total Pages Processed (Other): 13473
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 179111
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 4052885 (9.4052885)
我想原因是这样：
trace文件的Current SQL 根据login_id给update LOGOUT_TIME、ROLE_LEVEL、MONEY 等等字段(此步执行计划走主键索引)，因为logout_time是另一索引字段，维护索引LOG_RECORD_DETAIL_IX2，但LOG_RECORD_DETAIL_IX2索引中无此记录，就报错了
我以前的解决方式是
alter index owner.LOG_RECORD_DETAIL_IX2 REBUILD PARTITION P_LOG_ID_184 online
begin
dbms_stats.gather_index_stats( 'owner',indname => 'LOG_RECORD_DETAIL_IX2' ,partname => 'P_LOG_ID_184');
end;
/
最近已经发生3回了，会不会有更深入的原因没有找到？
206
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
