Oracle database Locks checking

  Oracle Database Locks checking

Issue coming to our system and its reporting with the Oracle Error which says 

Caused By: Error executing SQL ALTER TABLE ***

Caused By: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The most common reason for this are either 'SELECT FOR UPDATE ' or some uncommitted INSERT statements.

For checking we found the sql script which will provide the information what causing the issue.

set linesize 125
set pages 9999
set colsep |
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME 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;

OUTPUT 

OBJECT_NAME         |       SID|   SERIAL#|SPID                    |PROGRAM             |SQL_FULLTEXT                     |LOGON_TIME

--------------------|----------|----------|------------------------|--------------------|--------------------------------------------------------------------------------|---------------

HSSKSKKKS       732|     47771|63613                   |JDBC Thin Client    |INSERT INTO JJKKSLLSLLS VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:|03-AUG-21

ON                  |          |          |                        |           

---

select s.sid, s.serial#, p.spid

from

   v$session s,

   v$process p

where

   s.paddr = p.addr

and

   s.sid in (select SESSION_ID from v$locked_object);

   

   ----

select sid,serial#,username from v$session where sid IN(select blocking_Session from v$session);