How do I find what is causing ORA-00054?
ora-00054, oracle, oracle11g
Solution
from here: ORA-00054: resource busy and acquire with NOWAIT specified
You can also look up the sql,username,machine,port information and get to the actual process which holds the connection
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;
Problem
For past some time I have noticed that we get `ORA-00054` error while trying to issue `SELECT ... FOR UPDATE NOWAIT`, during large number of concurrent updates to the db. This our development system and we really do not have any other user, or at least that is what we believe. We have been through the logs of our application and it seems everything is in order; no threads are trying to update the same row. How can I configure Oracle db to generate a log on which would let me know the user id which holds the lock when this error occurred?