ORA-00054
ORA-00054 is the error code for the failed DDL operation attempt while someone was holding the lock. The main difficulty in dealing with this error is that the error message doesn’t show the blocker. I’m going to outline several methods for identifying the root cause.
We can simulate this problem by performing a DDL in the victim session while keeping an uncommitted change in the blocking session.
Blocker:
@whoami
SPID SID SERIAL# PID PROCESS DBUSER
------------------------ ---------- ---------- ---------- ------------------------ --------------------------------------------------------------------------------------------------------------------------------
1768940 17 20768 48 1768938 SYS
create table table123 (n number);
insert into table123 values (1);Victim:
SQL> @whoami
SPID SID SERIAL# PID PROCESS DBUSER
------------------------ ---------- ---------- ---------- ------------------------ --------------------------------------------------------------------------------------------------------------------------------
1884653 335 14388 51 1884651 SYS
truncate table table123 ;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expiredData dictionary
As for the first step, I’m checking if the blocker is still holding any locks:
SELECT session_id
FROM gv$locked_object v, dba_objects d
WHERE v.object_id = d.object_id and object_name = 'TABLE123' ;
SESSION_ID
----------
17That’s all you need to do if the blocker hasn’t released the lock yet.
Unknown object name
In the example above, I knew the name of the locked table because I wrote the SQL statements. But 3rd party applications often obscure this information. In such cases, you can setup the errorstack trace which will provide more information whenever the ORA-00054 occurs:
ALTER SESSION SET EVENTS '54 trace name errorstack level 3';In the trace file, we can see the recursive lock statement including the object name for which the error was raised:
truncate table table123
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
----- Current SQL Statement for this session (sql_id=fmj06n13hfvdh) -----
LOCK TABLE "TABLE123" IN EXCLUSIVE MODE NOWAITWhile the errorstack trace shows the name of the locked object, it doesn’t tell us anything about the blocker. In contrast, system dump provides the full picture.
System dump
The following command configures generating a system dump on the first occurrence of the ORA-00054 error:
alter system set events '00054 trace name systemstate level 266, lifetime 1';The grep based on the object name reveals only the information about the victim:
grep -i table123 /u00/oracle/orabase/diag/rdbms/dkss65c/DKSS65C/trace/DKSS65C_ora_1884653_0001.trc
ObjectName: Name=LOCK TABLE "TABLE123" IN EXCLUSIVE MODE NOWAIT
ObjectName: Name=truncate table table123But with the search based on the object_id we can position ourselves on the entry related to the blocker:
select object_id from dba_objects where object_name='TABLE123' ;
OBJECT_ID
----------
26446
1 row selected.!grep -i 26446 DB_ora_1884653_0001.trc
ktubl redo: slt: 15 wrp: 1 flg: 0x0c08 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 26446 objd: 26446 tsn: 0]
DML LOCK: tab=26446 flg=11 chi=0By scrolling up, we can find out the address of the process state object that was holding the lock:
owner: 0x8ff54a00, proc: 0x97e54fc0Based on the process’ state object address (0x97e54fc0), we can get the Oracle process id:
SO: 0x97e54fc0, type: process (2), map: 0x9759e9b8
state: LIVE (0x4532), flags: 0x1
owner: (nil), proc: 0x97e54fc0
link: 0x97e54fe0[0x97e54fe0, 0x97e54fe0]
child list count: 9, link: 0x97e55030[0x94f92630, 0x85d87270]
conid: 1, conuid: 1, SGA version=(1,0), pg: 0
SOC: 0x9759e9b8, type: process (2), map: 0x97e54fc0
state: LIVE (0x99fc), flags: INIT (0x1)
(process) Oracle pid:48, ser:3611, calls cur/top: (nil)/0x965bd3f0Finally, we can identify the session id:
48: USER ospid 1821521 sid 17 ser 45047, waiting for 'SQL*Net message from client'The system dump analysis yields the result, but it’s awkward.
I prefer ASH because the analysis is more user friendly.
Active session history
Luckily, we don’t need to invent the wheel to query ASH. Tanel Poder’s script ash_wait_chains.sql displays the relationship between blockers and waiters.
No information about the blocker, though, was displayed at first.
@ash_wait_chains "session_id||' '||sql_opname" "session_id=335 and session_serial#=14388" &hour
-- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN FIRST_SEEN LAST_SEEN
------ ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------- -------------------
100% 4 0 -> 335 TRUNCATE TABLE 2022-06-22 15:04:55 2022-06-22 15:18:57The reason for the missing blocker information is that the truncate wasn’t waiting on the lock to be released – the error was thrown immediately.
But if we define a ddl_lock_timeout (default is zero), the blocker information will be recorded:
alter session set ddl_lock_timeout = 6 ;
Session altered.
SQL> truncate table table123 ;
truncate table table123
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
@ash_wait_chains "session_id||' '||sql_opname" "session_id=335 and session_serial#=14388" &hour
-- Display ASH Wait Chain Signatures script v0.6 BETA by Tanel Poder ( http://blog.tanelpoder.com )
%This SECONDS AAS WAIT_CHAIN FIRST_SEEN LAST_SEEN
------ ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------- -------------------
60% 6 0 -> 335 TRUNCATE TABLE -> [idle blocker 1,17,45047 (sqlplus@lsdba04i.vt.ch (TNS V1-V3))] 2022-06-22 15:28:21 2022-06-22 15:28:26Conclusion
In conclusion, getting the blocker information for the command that failed with “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired” isn’t trivial. There are several methods to achive this.
The straightforward way is to query v$locked_object. That will yield the result only if the blocker is still holding the lock. Otherwise, you have to use either ASH or system dump. The former seems more elegant but requires additional licenses.
The Oracle error message doesn’t show the affected object. If the application hides the failed statement, you can get it from errorstack trace.
Troubleshooting flow chart
One Comment