ORA-00054 and look who attempts to hold my objects?

Oracle provides various methods for maintaining database tables and indexes. In most cases, the latest versions of Oracle provide syntax for online operations. However, in some cases it is necessary to perform offline maintenance operations that require exclusive access to a table or index. When doing so, an ORA-00054 error could occur.

Consider an example of reorganizing the index HR.EMP_EMP_PK.

In session A, run an active transaction:

update hr.employees set department_id=200 where department_id=100;

We’ll not invoke the commit clause yet.

In session B, run the command to rebuild the index

SQL> alter index hr.emp_emp_id_pk rebuild;
alter index hr.emp_emp_id_pk rebuild
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

An error occured.

How to find the session(s) started a transaction?

We would find object IDs:

SELECT
    o.object_id
FROM
    dba_objects  o,
    dba_indexes  i
WHERE
        o.owner = i.table_owner
    AND o.object_name = i.table_name
    AND i.owner = 'HR'
    AND i.index_name = 'EMP_EMP_ID_PK'
UNION ALL
SELECT
    object_id
FROM
    dba_objects
WHERE
        owner = 'HR'
    AND object_name = 'EMP_EMP_ID_PK';

 OBJECT_ID
----------
     81873
     81875

Therefore session ID, season serial# and instance number:

SELECT
    s.inst_id,
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    'ALTER SYSTEM KILL SESSION '''
    || s.sid
    || ','
    || s.serial#
    || ',@'
    || s.inst_id
    || ''';'
FROM
    gv$session        s,
    gv$locked_object  lo
WHERE
        s.inst_id = lo.inst_id
    AND s.sid = lo.session_id
    AND lo.object_id IN ( 81873, 81875 );

Knowing the attributes of the sessions, we can inform the user who run it or stop these sessions with the SYSDBA rights.