ora-08102 and col_usage$
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: ora-08102 and col_usage$
今天群里以网友提问说遇到ORA-08102错误，该错误报错如下：
ORA-08102: index key not found, obj# 518, file 1, block 4132 (3)
据说是在分析表时发生的，该网友的提问地址如下：shutdown时出现错误ora-08102
让网友直接drop 该index，然后重建报错：
|
1 2 3 4 5 |
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和对于的表结构如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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条信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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成功：
|
1 2 3 4 5 |
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环境中测试，发现：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
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对象有关。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
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$信息，如下测试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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]
One Response to “ora-08102 and col_usage$”
good
Leave a Reply
You must be logged in to post a comment.