ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
Oracle users who attempt to execute a LOCK TABLE or SELECT FOR UPDATE command with the NOWAIT keyword might run into the “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired” error. This means the table is already locked by another query. Typically, if the user waits a few minutes and tries to run the query again, the active session will have finished and the error message won’t reappear.
If the error message persists, the user will need to run a command to apply the DDL that’s continuing to fail. A common Oracle suggestion is a 11g parameter ddl_lock_timeout that would potentially force a DDL attempt to automatically retry the DDL request for a specified amount of time before timing out. Our DBAs attempted this when encountering the “Ora-00054” error while working through a deployment involving DDL against an active system during a planned release window, and it allowed the DDL to succeed. They’ve now incorporated this ALTER SESSION command in their deployment scripts with good success.
-- Set ddl_lock_timeout to 5 minutes to help with DDLs on an active tables and hopefully
-- prevent ORA-00054: resource busy and acquire with NOWAIT specified
alter session set ddl_lock_timeout=300;
This parameter was introduced in 11.1 and can be set at instance or session level and sets the number of seconds a DDL command will wait in the DML lock queue for the resource to become available before throwing the ORA-00054.
Another option is to find the active session and kill it to free up the table.
To identify the session that needs to be killed, use the [G]V$SESSION and [G]V$PROCESS views as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
--s.sql_id,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)
SQL>
Once you’ve found the active session, you can kill it to free up the table and run your query.
SQL>alter system kill session ‘sid, serial#’;
Preventing this error from continuously occurring is an important aspect of ensuring high business performance. Read our use case to learn how optimal database management can improve business processes. With time, consistent backup and recovery will help you avoid errors like ORA-00054. To lower the chances of an ORA-00054 error, one option is making your tables read only. To make your tables read only, enter the following:
SQL> alter table emp add (cust_id varchar2(3));
To alter the session table, execute the following:
SQL>alter table emp read only;
SQL> alter table emp add (cust_id varchar2(3));
Other tips users can use to avoid seeing the ORA-00054 error in the future include executing DDL during off-peak hours or during a planned maintenance hour, and identifying and killing the query that continuously prevents the exclusive lock.
To learn more about the solutions Solvaria provides in response to errors like this, visit our Solutions page.
Note: This blog is a follow up from our Oracle Team Lead Greg Mays’ previous post on the ORA-00054 error published here.
Share on
