Sometimes you might get the error “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired” and you want to know the detail of the session and query that blocking the session. You can use the following query to find out detail:

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;