问题描述
今天群里以网友提问说遇到ORA-08102错误，该错误报错如下：
ORA-08102: index key not found, obj# 518, file 1, block 4132 (3)
据说是在分析表时发生的，该网友的提问地址如下：shutdown时出现错误ora-08102
让网友直接drop 该index，然后重建报错：
SQL> create unique index I_COL_USAGE$ on col_usage$(obj#,intcol#) storage(maxextents unlimited); create unique index I_COL_USAGE$ on col_usage$(obj#,intcol#) storage(maxextents unlimited) * ERROR at line 1: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
很明显，从上述错误来看，是重复值了，不满足创建unique index的条件。该index和对于的表结构如下：
create table col_usage$ ( obj# number, /* object number */ intcol# number, /* internal column number */ equality_preds number, /* equality predicates */ equijoin_preds number, /* equijoin predicates */ nonequijoin_preds number, /* nonequijoin predicates */ range_preds number, /* range predicates */ like_preds number, /* (not) like predicates */ null_preds number, /* (not) null predicates */ timestamp date /* timestamp of last time this row was changed */ ) storage (initial 200K next 100k maxextents unlimited pctincrease 0) / create unique index i_col_usage$ on col_usage$(obj#,intcol#) storage (maxextents unlimited) /
网友执行如下sql查询，发现有85条信息：
SQL> select INTCOL#, OBJ# from col_usage$ group by intcol#,obj# having count(*)>1; OBJ# INTCOL# ---------- ---------- 4294952044 4 4294952426 4 4294952646 11 4294951384 4 4294951394 5 4294951460 13 .....省略部分信息 4294951460 4 4294951563 13 4294951850 3 4294951981 4 4294952034 3 4294952034 4 85 rows selected.
专家解答
我们知道col_usage$主要是为cbo服务的，换句话说里面存的是跟统计信息相关的，准确一点说是
存的sql查询时where 条件后谓词列相关的列统计信息使用情况，正常情况下，该表的数据在数据
库shutdown immediate 或 shutdown normal模式关闭后会被purge 清空的，正因为会在shutdown
时被清空，这里也就可能会出现一些问题，不过在10g以及以后版本已经没有这个问题了，后面会
详细描述（10g以后开始数据库shutdown 不会清空col_usage$)。
我这里让网友执行SQL 如下清空col_usage$数据，然后再次创建unique index成功：
delete from sys.col_usage$ c where not exists (select /*+ unnest */ 1 from sys.obj$ o where o.obj# = c.obj#);
补充一下，也可以用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 进行对col_usage$的清理。
我在我的10g vm环境中测试，发现：
SQL> select max(obj#) from obj$; MAX(OBJ#) ---------- 56649 SQL> select obj#,INTCOL# from col_usage$ where obj# >56649; OBJ# INTCOL# ---------- ---------- 4294950955 2 4294950955 3 4294950957 4 4294950988 2 4294950988 3 4294950989 3 4294950993 4 .....省略部分信息 4294952714 3 4294952714 4 4294952714 6 206 rows selected. SQL> show user USER is "SYS" SQL> delete from sys.col_usage$ c 2 where not exists (select /*+ unnest */ 3 1 4 from sys.obj$ o 5 where o.obj# = c.obj#); 206 rows deleted. SQL> commit; Commit complete. SQL> select obj#,INTCOL# from col_usage$ where obj# >56649; no rows selected SQL> ----这中间间隔几分钟 SQL> select count(*) from col_usage$ where obj# >56649; COUNT(*) ---------- 82 SQL> select count(obj#) from fixed_obj$; COUNT(OBJ#) ----------- 604
我通过查询v$sqlarea 发现了蛛丝马迹，如下了如下sql：
SELECT /*+ ordered use_nl(o c cu h) index(u i_user1) index(o i_obj2) index(ci_obj#) index(cu i_col_usage$) index(h i_hh_obj#_intcol#) */ C.NAME COL_NAME, C.TYPE# COL_TYPE, C.CHARSETFORM COL_CSF, C.DEFAULT$ COL_DEF, C.NULL$ COL_NULL, C.PROPERTY COL_PROP, C.COL# COL_UNUM, C.INTCOL# COL_INUM, C.OBJ# COL_OBJ, C.SCALE COL_SCALE, H.BUCK ET_CNT H_BCNT, (T.ROWCNT - H.NULL_CNT) / GREATEST(H.DISTCNT, 1) H_PFREQ, C.LENGTH COL _LEN, CU.TIMESTAMP CU_TIME, CU.EQUALITY_PREDS CU_EP, CU.EQUIJOIN_PREDS CU_EJP, C U.RANGE_PREDS CU_RP, CU.LIKE_PREDS CU_LP, CU.NONEQUIJOIN_PREDS CU_NEJP, CU.NULL_ PREDS NP FROM SYS.USER$ U, SYS.OBJ$ O, SYS.TAB$ T, SYS.COL$ C, SYS.COL_USAGE$ CU, SYS.HIST_HEAD$ H WHERE :B3 = '0' AND U.NAME = :B2 AND O.OWNER# = U.USER# AND O.TYPE# = 2 AND O.NAME = :B1 AND O.OBJ# = T.OBJ# AND O.OBJ# = C.OBJ# AND C.OBJ# = CU.OBJ#(+) AND C.INTCOL# = CU.INTCOL#(+) AND C.OBJ# = H.OBJ#(+) AND C.INTCOL# = H.INTCOL#(+) UNION ALL SELECT /*+ ordered use_nl(c) */ C.KQFCONAM COL_NAME, C.K QFCODTY COL_TYPE, DECODE(C.KQFCODTY, 1, 1, 0) COL_CSF, NULL COL_DEF, 0 COL_NULL, 0 COL_PROP, C.KQFCOCNO COL_UNUM, C.KQFCOCNO COL_INUM, O.KQFTAOBJ COL_OBJ, DECOD E(C.KQFCODTY, 2, -127, 0) COL_SCALE, H.BUCKET_CNT H_BCNT, (ST.ROWCNT - NULL_CNT) / G REATEST(H.DISTCNT, 1) H_PFREQ, DECODE(C.KQFCODTY, 2, 22, C.KQFCOSIZ) COL_LEN, CU. TIMESTAMP CU_TIME, CU.EQUALITY_PREDS CU_EP, CU.EQUIJOIN_PREDS CU_EJP, CU.RANGE_P REDS CU_RP, CU.LIKE_PREDS CU_LP, CU.NONEQUIJOIN_PREDS CU_NEJP, CU.NULL_PREDS NP FROM SYS.X$KQFTA O, SYS.TAB_STATS$ ST, SYS.X$KQFCO C, SYS.COL_USAGE$ CU, SYS.HIS T_HEAD$ H WHERE :B3 != '0' AND :B2 = 'SYS' AND O.KQFTANAM = :B1 AND O.KQFTAOBJ = ST.OBJ#(+) AND O.KQFTAOBJ = C.KQFCOTOB AND C.KQFCOTOB = CU.OBJ#(+) AND C.KQFCOC NO = CU.INTCOL#(+) AND C.KQFCOTOB = H.OBJ#(+) AND C.KQFCOCNO = H.INTCOL#(+)
里面关联了X$KQFTA 和 X$KQFCO，这两个x$ 表都是跟fixed对象有关的，所以我这里断定
col_usage$的obj#非常大的对象是跟fixed对象有关。
SQL> select max(obj#) from obj$; MAX(OBJ#) ---------- 56676 SQL> select count(*) from fixed_obj$; COUNT(*) ---------- 604 SQL> select count(*) from X$KQFTA; COUNT(*) ---------- 604 SQL> select count(*) from X$kqfta; COUNT(*) ---------- 604 SQL> select count(distinct KQFCOTOB) from X$KQFCO; COUNT(DISTINCTKQFCOTOB) ----------------------- 604 SQL> select count(*) from col_usage$; COUNT(*) ---------- 3181 SQL> set pagesize 300 SQL> l 1 select a.addr, 2 a.kqftaobj, 3 a.KQFTAVER, 4 a.KQFTANAM, 5 a.KQFTATYP, 6 a.KQFTACOC, 7 b.intcol# 8 from X$KQFTA a, col_usage$ b 9 where a.kqftaobj = b.obj# 10* and b.obj# > 56676 SQL> / ADDR KQFTAOBJ KQFTAVER KQFTANAM KQFTATYP KQFTACOC INTCOL# -------- ---------- ---------- ------------------------------ ---------- ---------- ---------- 0C943724 4294950955 2 X$KCBWAIT 1 5 2 0C943724 4294950955 2 X$KCBWAIT 1 5 3 0C943EA4 4294950957 12 X$KCFIO 2 22 4 0C945E84 4294950988 5 X$KGLST 1 16 2 0C945E84 4294950988 5 X$KGLST 1 16 3 0C945C68 4294950989 4 X$KQRST 1 22 3 0C940268 4294950993 13 X$KSLLT 4 41 4 0C940394 4294950994 3 X$KSLLD 4 7 2 0C940394 4294950994 3 X$KSLLD 4 7 3 0C940D6C 4294950995 4 X$KSMSD 4 5 3 0C940DA8 4294950997 2 X$KSMSS 4 6 4 0C941438 4294950998 5 X$KSPPI 4 9 2 0C941438 4294950998 5 X$KSPPI 4 9 3 0C941438 4294950998 5 X$KSPPI 4 9 4 0C9406DC 4294951004 25 X$KSUSE 2 84 2 0C9406DC 4294951004 25 X$KSUSE 2 84 3 0C9406DC 4294951004 25 X$KSUSE 2 84 4 0C9406DC 4294951004 25 X$KSUSE 2 84 23 0C9406DC 4294951004 25 X$KSUSE 2 84 24 0C9406DC 4294951004 25 X$KSUSE 2 84 27 0C9406DC 4294951004 25 X$KSUSE 2 84 31 0C9406DC 4294951004 25 X$KSUSE 2 84 73 0C940754 4294951005 14 X$KSUPR 2 44 2 0C940754 4294951005 14 X$KSUPR 2 44 4 0C940754 4294951005 14 X$KSUPR 2 44 25 0C940844 4294951008 6 X$KSUSGSTA 4 8 3 0C940844 4294951008 6 X$KSUSGSTA 4 8 6 0C946460 4294951023 2 X$KZDOS 1 6 4 0C94649C 4294951024 2 X$KZSRO 1 4 4 0C9464D8 4294951025 3 X$KZSPR 4 4 3 0C9401F0 4294951036 5 X$KQFCO 4 16 1 0C9401F0 4294951036 5 X$KQFCO 4 16 5 0C9401F0 4294951036 5 X$KQFCO 4 16 6 0C942E3C 4294951037 6 X$KCCFN 5 14 3 0C942E3C 4294951037 6 X$KCCFN 5 14 4 0C942E3C 4294951037 6 X$KCCFN 5 14 5 0C942E3C 4294951037 6 X$KCCFN 5 14 6 0C942E3C 4294951037 6 X$KCCFN 5 14 9 0C942E3C 4294951037 6 X$KCCFN 5 14 10 0C942C5C 4294951038 7 X$KCCDI 4 69 3 0C942C5C 4294951038 7 X$KCCDI 4 69 39 ......省略部分内容 0C948800 4294952567 1 X$KEWSSVCV 5 8 3 0C948800 4294952567 1 X$KEWSSVCV 5 8 6 0C948800 4294952567 1 X$KEWSSVCV 5 8 7 0C941384 4294952646 1 X$KSMPGST 4 11 6 0C941384 4294952646 1 X$KSMPGST 4 11 9 0C941384 4294952646 1 X$KSMPGST 4 11 10 0C941384 4294952646 1 X$KSMPGST 4 11 11 0C94022C 4294952712 1 X$KQFOPT 1 5 4 0C94022C 4294952712 1 X$KQFOPT 1 5 5 0C947054 4294952714 1 X$QKSBGSES 5 13 3 0C947054 4294952714 1 X$QKSBGSES 5 13 4 0C947054 4294952714 1 X$QKSBGSES 5 13 6 217 rows selected. SQL> select count(*) from col_usage$ where obj# > 56676; COUNT(*) ---------- 227 SQL> select obj# 2 from col_usage$ 3 where obj# not in (select kqftaobj from x$kqfta) 4 and obj# > 56676; OBJ# ---------- 4294951073 4294951073 4294952680 4294952683 4294952683 4294952683 4294952684 4294952684 4294952684 4294952684 10 rows selected.
我们可以看到col_usage$里面部分obj#非常大的一部分实际上就是x$表的相关信息。
但是上面10条多出来的信息，我还不知道是怎么回事，大家一起研究一下。
另外，10g开始，db shutdown不会purge 清理col_usage$信息，如下测试：
SQL> show user USER is "SYS" SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1272600 bytes Variable Size 142607592 bytes Database Buffers 20971520 bytes Redo Buffers 2920448 bytes Database mounted. SQL> alter database open; Database altered. SQL> SQL> select count(*) from col_usage$; COUNT(*) ---------- 3184
关于col_usage$的几篇mos文档，大家可以参考一下：
Column usage in Multi Column Index [ID 400214.1]
DBMS_STATS With METHOD_OPT =>’..SIZE auto’ May Not Collect Histograms [ID 557594.1]
Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort [ID 332177.1]
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
