Question: What is the cause of the Oracle error “ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired”?
The error is raised when the following statement is executed against the Oracle Database.
- SQL> ALTER TABLE vst.account ADD (update_date varchar2(100));
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
Answer: The Oracle error utility oerr shows this description for the ORA error.
00054, 00000, “resource busy and acquire with NOWAIT specified or timeout expired”
Interested resource is busy.
Retry if necessary or increase timeout.
Cause: The statement requires an exclusive lock on the table.
If the table is active and has DML or DDL transactions against it the transaction will not be able to acquire the lock quickly. This will cause the transaction to fail and return the resource busy error to the session.
There are three workarounds to address this.
You can increased the timeout by modifying the DDL_LOCK_TIMEOUT parameter in your session. Once this is set Oracle will wait for the new TIMEOUT before returning the “ORA-00054: resource busy and acquire with NOWAIT specified” error.
- SQL> ALTER SESSION SET ddl_lock_timeout=900;
SQL> ALTER TABLE vst.account ADD (update_date varchar2(100));
Using a LOOP
CREATE PROCEDURE ddl_loop(run_sql varchar2)
PRAGMA exception_init(in_use, -54);
WHILE true LOOP
EXECUTE IMMEDIATE run_sql;
WHEN in_use THEN null;
Kill Session Or Shutdown Application
Who is holding the lock? To determine what Session and SQL is operating against the object, run a query against the v$access and the v$session tables to obtain this information.
SELECT a.object, a.type, a.sid,
FROM v$access a, v$session s
WHERE a.sid = s.sid
AND a.owner = ‘&owner’
AND a.object = ‘&object_name’;
After determining the user who is accessing the table, determine if the DML being performed can be stopped temporarily or the session killed.
• RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP – When trying to restore a controlfile I am getting the RMAN-06563 error. I have a backed up the controlfile but the RMAN is not able to see it. Why is this happening?
• Proxy User Authentication with Connect Through in Oracle Database – The Proxy authentication mechanism allows a user to login as their own user but land into a different schema without knowing the password for that user. For example the USER_B may want to create an object in the SCHEMA_A or want to use the privileges/roles of the SCHEMA_A user.
• Oracle 12c RAC: New Features Oracle has come a long way from its inception of the 9i RAC database to its current 12c version. I remember the days when we had to deal with RAC related issues, ranging from performance to stability which gradually improved as this product matured. Learn more about the new feature now available with RAC.
• Connecting Applications from Desktop to your VirtualBox Database If you are trying to connect between VirtualBox Machines or trying to setup the network to communicate from the Desktop to the VirtualBox machines, follow the simple to use instructions here
Note: This Oracle documentation was created for reference for use by Vitalsofttech DBA’s. If you have any questions please post by clicking on the ASK A QUESTION link above.