示例：Oracle创建索引时中断，再次创建报错ORA-00955，rebuild报错ORA-08104，使用DBMS_REPAIR.ONLINE_INDEX_CLEAN解决

创建测试环境

create table t as select * from dba_objects;
insert into t select * from t;       --多次执行，模拟大数据量，这样创建索引时间长，容易模拟故障

SQL> select count(*) from t;

  COUNT(*)
----------
  11041152


exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&TOWN' , tabname => '&TNAME' , cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT',  degree => 8,no_invalidate=>false); 

模拟故障

第一个session创建索引
create index t_ind_id_name on t(OBJECT_ID,OBJECT_NAME) online; 

同时在第二个session不断的插入数据
insert into t select * from t where rownum<100;

kill掉第一个session，再次创建索引，或出现以下 ORA-00955 的报错

SQL> create index t_ind_id_name on t(OBJECT_ID,OBJECT_NAME) online;
create index t_ind_id_name on t(OBJECT_ID,OBJECT_NAME) online
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

rebuild索引，会报ORA-08104的错误

SQL> alter index t_ind_id_name rebuild online; 
alter index t_ind_id_name rebuild online
*
ERROR at line 1:
ORA-08104: this index object 87386 is being online built or rebuilt

drop index报ORA-00054的错误，实验中是因为insert操作没有做commit
SQL> drop index t_ind_id_name;
drop index t_ind_id_name
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

drop index也会报 ORA-08104 错误

SQL> drop index t_ind_id_name;
drop index t_ind_id_name
           *
ERROR at line 1:
ORA-08104: this index object 87386 is being online built or rebuilt

解决

使用DBMS_REPAIR.ONLINE_INDEX_CLEAN清除创建索引的临时对象

conn / as sysdba
DECLARE
isClean BOOLEAN;
BEGIN
isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(87386);     --ORA-08104(87386)
END;
/