How to Fix | ORA-00054: Resource busy error

ORA-00054 error message:

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

Causes

ORA-00054 error is a commonly seen error by Oracle users and occurs when a user tries to execute a LOCK TABLE or SELECT FOR UPDATE command with the NOWAIT keyword when the resource is unavailable. DDL or DML operations are being run concurrently without proper commits. In most cases, Error ORA-00054? occurs from a session. Any session that has referenced the table and any structural change attempt, such as adding a column, requires an “exclusive” lock.

Solutions

This happens when a session other than the one used to alter a table is holding a lock likely because of a DML (update/delete/insert). If you are developing a new system, it is likely that you or someone in your team issues the update statement and you could kill the session without much consequence. Or you could commit from that session once you know who has the session open.

If you have access to a SQL admin system use it to find the offending session. And perhaps kill it. You could use v$session and v$lock and others but I suggest you google how to find that session and then how to kill it.

Actions

You have choices for avoiding the ORA-00054 error:

• Re-run the change late at night when the database is idle.
• Do all DDL during a maintenance window with all end-users locked-out.
• Kill the sessions that are preventing the exclusive lock. 

From above three options the easiest and the quickest method is the kill the blocking session. Use below query to check active session info. There are some other sessions which are blocking this sessions.

SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
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;

alter system kill session 'SID,SERIAL#';

Screenshots

ORA-00054 error when executing DML.

Find blocking session

Kill the blocking session

After session kill, type the SQL again it will be execute successfully.

Oh, that’s a way of the How to solve | ORA-00054: ‘resource busy and acquire with NOWAIT specified or timeout expired’ on my website at this time. If you have any suggestions for additional issues just leave a comment below, and I’ll see what I can do. You can find more Oracle related articles from here.

Please feel free to share this post with anyone who might be interested..

About Author