sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ora-08102 col_usage$原因和MOS解决办法

时间:2016-12-05 21:55   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库分析表时遇到ORA-08102: index key not found, obj# 518, file 1, block 4132 (3)错误。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 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,然后重建报错:

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> 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                  12http://www.oracleplus.net72600 bytes
Variable SIZE             142607592 bytes
DATABASE Buffers           20971520 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
SQL> ALTER DATABASE OPEN;

DATABASE altered.

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]

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ora-08102 col_usage$原因和MOS解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1369.html

Oracle研究中心

关键词:

Oracle报错ORA-08102

Oracle col_usage$异常

ORA-01452

Oracle创建unique index不成功的解决办法