Monday, October 11, 2010

How to reslove ORA-08104 : this index object 75350 is being online built or rebuilt

SQL> alter index S.SYS_C007740 rebuild online tablespace s_data01;
alter index S.SYS_C007740 rebuild online tablespace s_data01
*
ERROR at line 1:
ORA-08104: this index object 75350 is being online built or rebuilt

SQL> conn / as sysdba
Connected.
SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2563


SQL> declare
2 isclean boolean;
3 begin
4 isclean :=false;
5 while isclean=false
6 loop
7 isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(dbms_repair.all_index_id,dbms_repair.lock_wait);
8 dbms_lock.sleep(10);
9 end loop;
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> select obj#,flags from ind$ where obj#=75350;

OBJ# FLAGS
---------- ----------
75350 2051


SQL> conn donghua2/donghua2
Connected.
SQL> alter index S.SYS_C007740 rebuild online tablespace s_data01;

Index altered.