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
- Killing a session rolls back its uncommitted transaction. Check with the application owner before doing it.
- A session in the
INACTIVEstate can still hold locks if it has an open transaction.