由于系统负载较高，online重建索引太慢，就直接kill掉该session，准备删除索引再进行重建，此时发觉无法对其进行删除和重建。 sql> drop index call.ind_id; drop index call.ind_id * error at line 1: ora-08104: this index object 179685 is being onlin
由于系统负载较高，online重建索引太慢，就直接kill掉该session，准备删除索引再进行重建，此时发觉无法对其进行删除和重建。
sql> drop index call.ind_id;
drop index call.ind_id
*
error at line 1:
ora-08104: this index object 179685 is being online built or rebuilt
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';
no rows selected
sql> drop index ind_test;
drop index ind_test
*
error at line 1:
ora-01418: specified index does not exist
原文地址：online index create fail引起ora-08104, 感谢原作者分享。