Oracle wait interface - ORA-00054: resource busy

SQL> ALTER INDEX ESMDBA.ESM_PRCS_ERR_IDX1 rebuild;
ALTER INDEX ESMDBA.ESM_PRCS_ERR_IDX1 rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

1. check the index on which table

SQL> select OWNER,INDEX_NAME,TABLE_NAME,INDEX_TYPE from dba_indexes where INDEX_NAME like '%ESM_PRCS_ERR_IDX1%';

OWNER INDEX_NAME TABLE_NAME INDEX_TYPE

------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------

ESMDBA ESM_PRCS_ERR_IDX1 ESM_PRCS_ERR NORMAL

2. check which session is using the table

select object_name,s.sid,s.serial#,p.spid
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

OBJECT_NAME SID SERIAL# SPID
------------------------------ ---------- ---------- ------------
PLAN_TABLE 622 15849 26577
ESM_PRCS_ERR 892 2002 8309
ESM_PRCS_ERR 490 24755 8403
ESM_PRCS_ERR 396 60589 8388
ESM_PRCS_ERR 395 24011 8353

3. find the session and check it can be killed or not ? confirm with Applications.

set linesize 150
set pagesize 999

SELECT sql_text FROM
v$session, v$sqltext_with_newlines
WHERE decode(v$session.sql_hash_value, 0, prev_hash_value, sql_hash_value) = v$sqltext_with_newlines.hash_value
AND v$session.sid = &sid
ORDER BY piece;

Enter value for sid: 892
old 5: AND v$session.sid = &sid
new 5: AND v$session.sid = 892

SQL_TEXT
----------------------------------------------------------------
SELECT ERR_OID, MQ_MSGE FROM ESM_PRCS_ERR WHERE ERR_OID = :1 FOR UPDATE NOWAIT

SQL> /
Enter value for sid: 490
old 5: AND v$session.sid = &sid
new 5: AND v$session.sid = 490

SQL_TEXT
----------------------------------------------------------------
SELECT ERR_OID, MQ_MSGE FROM ESM_PRCS_ERR WHERE ERR_OID = :1 FOR UPDATE NOWAIT

4. kill these sessions and try to rebuild it again.

alter system kill session '892,2002';

alter system kill session '490,24755';