建立索引失败，删除索引报错ora-08104错误，测试

面对这种问题：

由于PP表的数据量大概有3000多万,执行到一半的时候由于网络问题,内网跟外网断开,导致这个建立索引的操作终止,现在查看这个PP表的INDEX,已经有这个索引存在,想rebuild这个索引,但是报如下错误:
sqlplus >alter index IDX_ON_PP_cpe  rebuild;
ORA-08104 this index object 7732 is being online built or rebuilt
sqlplus >drop index IDX_ON_PP_cpe;
ORA-08104 this index object 7732 is being online built or rebuilt

ORA-08104 错误描述如下:
ORA-08104 this index object string is being online built or rebuilt

解决方案：

第一：通过重启可以

第二：通过修改内部字典表可以释放锁！

做了一个测试（因为是生产库，采用第二种，修改字典表）

建立索引：

SQL> conn mobile_v1/*******

SQL> create table meng (phone varchar2(24));

table created

SQL> create index idx_meng_phone on meng (phone);
 
Index created
 

如果索引正常建立：

SQL> explain plan for select * from meng where phone='135666';

 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 559194673
--------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |     1 |     7 |     1   (0)| 00:00:0
|*  1 |  INDEX RANGE SCAN| IDX_MENG_PHONE |     1 |     7 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("PHONE"='135666')
Note
-----
   - dynamic sampling used for this statement (level=2)
 
17 rows selected

 

如果索引建立失败：

索引还存在

SQL> select index_name,index_type,table_owner from user_indexes where table_name='MENG';
 
INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER
------------------------------ --------------------------- ------------------------------
IDX_MENG_PHONE                 NORMAL                      MOBxx
 

以下是执行计划 （有索引为什么，还走table all scan？，那是因为索引建立过程中被异常中断）

SQL> explain plan for select * from meng where phone='13324';
 
Explained
 
SQL> SELECT * FROM table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3605692949
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    12 |    25   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MENG |     1 |    12 |    25   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PHONE"='13324')
 
13 rows selected
 

这时，drop index idx_meng_phone;

     报错 ora-08104 

处理办法：

SQL> sqlplus / as sysdba

connected

SQL> select distinct flags from ind$;


     FLAGS
----------
      2082
         6
  67108864
  67110946
  67108870
  67110914
         2
      2178
  67108866
      2050
      2054


     FLAGS
----------
      2210
       256
    264194
         0

SQL> select flags from ind$  
  2   where obj# IN (select obj# from obj$ 
                  where owner#=(select user# from user$ 
                                where name='MOBILE_V1'
                              ) and 
                        name='IDX_MENG_PHONE'
               );  3    4    5    6    7  


     FLAGS
----------
       256

SQL> update   ind$  set flags =flags-100
  2   where obj# IN (select obj# from obj$ 
                  where owner#=(select user# from user$ 
                                where name='MOBILE_V1'
                              ) and 
                        name='IDX_MENG_PHONE'
               );  3    4    5    6    7  

此时，

SQL> sqlplus mobile_v1/******

connected

SQL> drop index idx_meng_phone;
 
Index dropped
 

问题解决

其实online rebuild或create失败后，oracle会做以下两件事情：
1.smon进程清除ind$基表，将相应的索引的flags更改为0.这步操作每隔一小时做一次。
2.smon进程清理临时段。每隔两小时做一次。

正常情况下，smon将flags改为0后，可以将索引删除。但是如果此时临时段还未清理完毕的话，还不能马上做rebuild online操作，

否则将会报出ora-08106的错误