How to catch the blocker of a record in a table

Hypothesis: i try yo update a record in a table and i receive, let's say, the following error message:

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

or something is telling me the record is locked. If the issue is spanning over too much time, i have to overcome somehow the situation.

As a DBA, i use this sql bullet:



select a.sid, a.serial#, a.inst_id, c.object_name, a.username,d.sql_text
from gv$session a, gv$locked_object b, dba_objects c , gv$sqlarea d
where b.object_id = c.object_id 
and a.sid = b.session_id and a.sql_id=d.sql_id and upper(object_name)='tablename';


As you can see, the environment is a clusterized one, the views are gv$, and there is some information in there that you can consider it redundant: sql_text. Not everytime  that column will point to the actual sql command guilty of locking. So, you can filter a bit:


select a.sid, a.serial#, a.inst_id, c.object_name, a.username
from gv$session a, gv$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id and a.sql_id=d.sql_id and upper(object_name)='tablename';


and, for standalone environment:


select a.sid, a.serial#, a.inst_id, c.object_name, a.username,d.sql_text
from v$session a, v$locked_object b, dba_objects c , v$sqlarea d
where b.object_id = c.object_id 
and a.sid = b.session_id and a.sql_id=d.sql_id and upper(object_name)='tablename';