My second attempt to rebuild below mentioned index failed with “ORA-08104-this index object 22624 is being online built or rebuilt”. I am getting this oracle error because my first attempt to rebuild same index failed due to abnormal session termination, it was incomplete rebuild.

Error log:

SQL> ALTER INDEX UPASSUSR.IDX_TRANSLOG_OBJECTNAME REBUILD online TABLESPACE UPASSTBS;
ALTER INDEX UPASSUSR.IDX_TRANSLOG_OBJECTNAME REBUILD online TABLESPACE UPASSTBS
*
ERROR at line 1:
ORA-08104: this index object 22624 is being online built or rebuilt

According to oracle support(Doc ID 375856.1) we can rid out of the issue by cleaning garbage with the help of dbms_repair.online_index_clean function. But no luck after successful execution of dbms_repair.online_index_clean function.

declare
lv_ret BOOLEAN;
begin
lv_ret := dbms_repair.online_index_clean(22624);
end;
/
PL/SQL procedure successfully completed.

Same clean-up logs popped up in ADRCI with same object no(i.e.22624) but no luck.

2016-09-27 13:21:06.373000 +05:30
online index (re)build cleanup: objn=22624 maxretry=2000 forever=0

Even, I tried dropping index and mark as unusable but unlucky again.

SQL> drop index UPASSUSR.IDX_TRANSLOG_OBJECTNAME;
drop index UPASSUSR.IDX_TRANSLOG_OBJECTNAME
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

So, I have decided to kill that session with the help of “ALTER SYSTEM KILL SESSION”, I got require details with the help of following SQL:

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT, 
S.LOGON_TIME, SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID 
AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS
AND O.OBJECT_NAME = 'AOUP_TRANS_LOG';

After 60 seconds of waiting, SQL command ended up with “ORA-00031: session marked for kill” oracle error. 🙁

SQL> ALTER SYSTEM KILL SESSION '3423,39597';
ALTER SYSTEM KILL SESSION '3423,39597'
*
ERROR at line 1:
ORA-00031: session marked for kill

Finally, I got the solution by killing process at OS level:
kill -9 spid     (from above)

Thank god, Index rebuild was successful.

Object flag before killing OS process:

SQL> select obj#,flags from ind$ where obj#=22624;
OBJ#       FLAGS
---------- ----------
22624      2562

Object flag after killing OS process:

SQL> select obj#,flags from ind$ where obj#=22624;
OBJ#       FLAGS
---------- ----------
22624      2050

Have a great time ahead.

Stay Tune. 🙂