ORA-08104 索引online rebuild

请看我在优快云上的此篇文章，写的更为详细：http://blog.youkuaiyun.com/jiujiuaiqi/article/details/6907576 


select object_id from dba_objects where object_name='NAQ_TRADE_ITEM_TRADENO_IND';

 OBJECT_ID
----------
    115225
查看ind$的创建语句，rdbms/admin/sql.bsq文件里  
create table ind$                                             /* index table */
( obj#          number not null,                            /* object number */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */
  dataobj#      number,                          /* data layer object number */
  ts#           number not null,                        /* tablespace number */
  file#         number not null,               /* segment header file number */
  block#        number not null,              /* segment header block number */
  bo#           number not null,              /* object number of base table */
  indmethod#    number not null,    /* object # for cooperative index method */
  cols          number not null,                        /* number of columns */
  pctfree$      number not null, /* minimum free space percentage in a block */
  initrans      number not null,            /* initial number of transaction */
  maxtrans      number not null,            /* maximum number of transaction */
  pctthres$     number,           /* iot overflow threshold, null if not iot */
  type#         number not null,              /* what kind of index is this? */
                                                               /* normal : 1 */
                                                               /* bitmap : 2 */
                                                              /* cluster : 3 */
                                                            /* iot - top : 4 */
                                                         /* iot - nested : 5 */
                                                            /* secondary : 6 */
                                                                 /* ansi : 7 */
                                                                  /* lob : 8 */
                                             /* cooperative index method : 9 */
  flags         number not null,     
                /* mutable flags: anything permanent should go into property */
                                                    /* unusable (dls) : 0x01 */
                                                    /* analyzed       : 0x02 */
                                                    /* no logging     : 0x04 */
                                    /* index is currently being built : 0x08 */
                                     /* index creation was incomplete : 0x10 */
                                           /* key compression enabled : 0x20 */
                                              /* user-specified stats : 0x40 */
                                            /* secondary index on IOT : 0x80 */
                                      /* index is being online built : 0x100 */
                                    /* index is being online rebuilt : 0x200 */
                                                /* index is disabled : 0x400 */
                                                     /* global stats : 0x800 */
                                            /* fake index(internal) : 0x1000 */
                                       /* index on UROWID column(s) : 0x2000 */
                                            /* index with large key : 0x4000 */
                             /* move partitioned rows in base table : 0x8000 */
                                 /* index usage monitoring enabled : 0x10000 */
                      /* 4 bits reserved for bitmap index version : 0x1E0000 */
  property      number not null,    /* immutable flags for life of the index */
                                                            /* unique : 0x01 */
                                                       /* partitioned : 0x02 */
                                                           /* reverse : 0x04 */
                                                        /* compressed : 0x08 */
                                                        /* functional : 0x10 */
                                              /* temporary table index: 0x20 */
                             /* session-specific temporary table index: 0x40 */
                                              /* index on embedded adt: 0x80 */
                         /* user said to check max length at runtime: 0x0100 */
                                              /* domain index on IOT: 0x0200 */
                                                      /* join index : 0x0400 */
                /* functional index expr contains a PL/SQL function : 0x0800 */
                           /* The index was created by a constraint : 0x1000 */
                              /* The index was created by create MV : 0x2000 */
online创建前：
select flags from ind$ where obj#=115225;

     FLAGS
----------
      2050
这个2050 可以这么解释：
2050=2+2048=16进制的 2+800
查看ind$的创建语句会知道：
2050代表：2+800=表被分析过(/* analyzed       : 0x02 */)+表是被dbms_stats分析的(/* global stats : 0x800 */)
表只有被dbms_stats分析过， global stats的状态才是yes.

online创建后：
select flags from ind$ where obj#=115225;

     FLAGS
----------
      2562
     
SELECT 2562-2050 FROM dual;
2562-2050
----------
       512

增加的512可以这么解释：
select to_char(512,'xxxxxx') from dual;

TO_CHAR(512,'X
--------------
    200
/* index is being online rebuilt : 0x200 */
索引正在被online重建

kill -9 16522
alter index hades.NAQ_TRADE_ITEM_TRADENO_Ind rebuild online;
ERROR:
ORA-03114: not connected to ORACLE

alter index hades.NAQ_TRADE_ITEM_TRADENO_Ind rebuild online
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 16522
Session ID: 1144 Serial number: 5555

alter index hades.NAQ_TRADE_ITEM_TRADENO_Ind rebuild online;
*
ERROR at line 1:
ORA-08104: this index object 115225 is being online built or rebuilt

   
DECLARE
  RetVal        BOOLEAN;
  OBJECT_ID     BINARY_INTEGER;
  WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
  OBJECT_ID     := 115225;
  WAIT_FOR_LOCK := NULL;
  RetVal        := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
  COMMIT;
END;
/
但是如果表上存在事务或是有TM锁，那么这个PL/SQL块会久久的HANG在那里。直到事务结束。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-708783/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-708783/