LLI Wiki — My Digital Knowledge Base

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Find locked objects, kill the blocking session and set DDL_LOCK_TIMEOUT to deal with ORA-00054.

ORA-00054 is raised when a DDL statement, or a SELECT ... FOR UPDATE NOWAIT, cannot get a lock on a resource, or when the lock wait times out.

Identify the locked objects

The following query matches each locked object with its session, operating system process and last SQL statement:

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;

A shorter version shows the lock mode held:

SELECT lo.session_id, lo.oracle_username, lo.os_user_name,
       lo.process, do.object_name,
       DECODE(lo.locked_mode, 0, 'None', 1, 'Null', 2, 'Row Share (SS)',
              3, 'Row Excl (SX)', 4, 'Share', 5, 'Share Row Excl (SSX)',
              6, 'Exclusive', TO_CHAR(lo.locked_mode)) mode_held
FROM v$locked_object lo, dba_objects do
WHERE lo.object_id = do.object_id
ORDER BY 1, 5;

Kill the blocking session

Get the SID and SERIAL# from V$SESSION, then kill the session:

SQL> SELECT sid, serial#, username, status FROM v$session WHERE sid = 271;

       SID    SERIAL# USERNAME                       STATUS
---------- ---------- ------------------------------ --------
       271       1409 APP_USER                       INACTIVE

SQL> ALTER SYSTEM KILL SESSION '271,1409' IMMEDIATE;

System altered.

Wait for the lock instead of failing

The DDL_LOCK_TIMEOUT parameter sets, in seconds, how long a DDL statement waits for a lock before raising the error:

ALTER SYSTEM SET ddl_lock_timeout = 20;

Notes

Mastodon LLIMastodon SocialFosstodon