onlineindexcreatefail引起ora-08104

SQL> alter index call.ind_id rebuild;
alter index call.ind_id rebuild
*
ERROR at line 1:
ORA-08104: this index object 179685 is being online built or rebuilt

SQL> alter index call.ind_id rebuild online;
alter index call.ind_id rebuild online
*
ERROR at line 1:
ORA-08104: this index object 179685 is being online built or rebuilt

报出了ora-08104错误，找到mos上的几篇文章
Session Was Killed During The Rebuild Of Index ORA-08104 (文档 ID 375856.1)

CAUSE
A session failure during an online index rebuild can leave the data dictionary in a state reflecting a rebuild is ongoing when in fact it is not.

SOLUTION
The dbms_repair.online_index_clean function has been created to cleanup online index rebuilds. Use the dbms_repair.online_index_clean function to resolve the issue.
* Please note if you are unable to run the dbms_repair.online_index_clean function it is due to the fact that you have not installed the patch for Bug 3805539 or are not running on a release that includes this fix. The fix for this bug is a new function in the dbms_repair package called dbms_repair.online_index_clean, which has been created to cleanup online index [[sub]partition] [re]builds. New functionality is not normally introduced in patchsets; therefore, this is not available in a patchset but is available in 10gR2.
- Check your patch list to verify the database is patched for Bug 3805539 using the following command and patch for the bug if it is not listed:

opatch lsinventory -detail

造成这个现象的原因是因为数据字典信息和实际信息冲突，数据字典中记录的是索引在重建，但是事实并不是如此

下面小鱼手动模拟这个故障的始末：
SQL> create table tab01 as select * from dba_tab_col_statistics;

Table created.
SQL> insert into tab01 select * from tab01;

179626 rows created.

SQL> /

359252 rows created.

SQL> commit;

Commit complete.

SQL> select spid from v$process where addr in (select paddr from v$session where sid=userenv('sid'));

SPID
------------
24990

Kill掉这个会话的ospid
SQL> create index ind_test on tab01(table_name,column_name,num_nulls) online;
create index ind_test on tab01(table_name,column_name,num_nulls) online
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

此时无法对这个索引进行删除、重建等
SQL> conn xiaoyu/xiaoyu
Connected.
SQL> drop index ind_test;
drop index ind_test
*
ERROR at line 1:
ORA-08104: this index object 125197 is being online built or rebuilt

数据字典中任然记录这个索引的信息
SQL> select status from user_indexes where index_name='IND_TEST';

STATUS
--------
VALID

SQL> select object_id from user_objects where object_name='IND_TEST';

OBJECT_ID
----------
125197

在oracle 10GR2后我们可以直接用dbms_repair.online_index_clean来进行清除，不再需要使用代价较大的重启数据库、修改字典表ind$等办法
SQL> DECLARE
2 RetVal BOOLEAN;
3 OBJECT_ID BINARY_INTEGER;
4 WAIT_FOR_LOCK BINARY_INTEGER;
5 BEGIN
6 OBJECT_ID := 125197;
7 WAIT_FOR_LOCK := NULL;
8 RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
9 COMMIT;
10 END;
11 /
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
*
ERROR at line 8:
ORA-06550: line 8, column 11:
PLS-00201: identifier 'SYS.DBMS_REPAIR' must be declared
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored

这里需要使用sysdba登录
SQL> conn / as sysdba
Connected.
SQL> DECLARE
2 RetVal BOOLEAN;
3 OBJECT_ID BINARY_INTEGER;
4 WAIT_FOR_LOCK BINARY_INTEGER;
5 BEGIN
6 OBJECT_ID := 125197;
7 WAIT_FOR_LOCK := NULL;
8 RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN();
9 COMMIT;
10 END;
11 /

PL/SQL procedure successfully completed.

再次登录发现字典表的索引信息已经清除了，也可以重新对索引进行重建
SQL> conn xiaoyu/xiaoyu
Connected.
SQL> select object_id from user_objects where object_name='IND_TEST';

no rows selected
SQL> select object_id from user_objects where object_name='IND_TEST';