创建测试环境
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;
/