今天群里以网友提问说遇到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]
发表回复