Sunday, November 7, 2010

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


$ sqlplus donghua2/donghua2

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 22 18:40:36 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

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




Errors in file /u01/app/oracle/admin/appprd/udump/appprd1_ora_25717.trc:
ORA-00600: internal error code, arguments: [kdtdelrow-2], [6], [6], [], [], [], [], []
ORA-01502: index 'APP_OWNER.SYS_C007740' or partition of such index is in unusable state



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 APP_OWNER.SYS_C007740 rebuild online tablespace sec_data01;