But I can not see error in alert.log. Is ora-0054 not being captured?
at java.lang.Thread.run(Thread.java:662) Caused by: java.sql.SQLException: ORA-00054: resource busy and acquire with NOWAIT specified
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode FROM dba_objects o, gv$locked_object l WHERE o.object_id = l.object_id; SELECT DECODE(request,0,'Holder: ','Waiter:') || sid sess, id1, id2, lmode, request, type FROM gv$lock WHERE (id1, id2, type) IN ( SELECT id1, id2, type FROM gv$lock WHERE request>0) ORDER BY id1, request; 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;
Which of the following 3 scripts captures the lock information correctly?Probably none of them. Your SQL is not being blocked (the SQL fails immediately with ORA-00054), so you're not going to be able to see a classic blocker/blockee situation. If I were trying to debug what's going on, I'd tell the developers, "let's please turn on some of that tracing/instrumentation that you wrote and we can see what SQL is being executed," but my experience in the world tells me the response you get will be "what's instrumentation?"
So to solve the issue I will do tracing?No.