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.
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. 🙂