Display the current locks in a tree-like fashion

Oracle provides some built-in scripts and views that can be used to monitor locks in the database


http://docs.oracle.com/database/121/ADMIN/monitoring.htm#ADMIN11255

To illustrate this, I will open a session that updates a table, then create another session that updates the same table:

Session# 1:

select SYS_CONTEXT('userenv','con_name') "container name",
       SYS_CONTEXT('userenv','con_id') "container id",
       SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
       SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          362

--Update a the table rows:
 update emp set sal=1000;

14 rows updated.

I will keep the session alive, without doing commit/rollback.

From session# 2:

select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema                 SID
-------------------- -------------------- ------------------------------ -----
VEGDB01              3                    SCOTT                          130

--Update the same rows as in session# 1:
update emp set sal=2000;

This session will not give you the prompt back, as it is trying to aquire a lock on the table rows.

I then ran the utllockt.sql script:

sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/utllockt.sql

Output (abbreviated):

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED  MODE_HELD     LOCK_ID1          LOCK_ID2
----------------- ----------------- --------------- ------------ ----------------- -----------------
362               None                
   130            Transaction       Exclusive       Exclusive     524308             17836

The leftmost session (362) is blocking the sessions listed underneath it.

Let's check what the session with SID = 130 is doing:

SELECT sess.sid,sess.serial#,sess.sql_id,s.plan_hash_value, s.child_number,sess.client_info,substr(sql_text,1,30) "sql text",sess.program,sess.pq_status,
        decode(sess.blocking_session_status,'VALID','blocked', 'NO HOLDER','Not blocked') "blocked?",
        sess.blocking_session "blocking session SID",sess.event
FROM V$SESSION sess inner join v$sql s 
on (sess.sql_id = s.sql_id)
WHERE sess.SID IN (130)
and sess.sql_child_number = s.child_number;

This query confirms the output from the Oracle supplied utllockt.sql, and reveals what the blocking session is trying to do, in this case, to execute DML on table rows as that are already being locked for update:

SID	SERIAL#	SQL_ID	PLAN_HASH_VALUE	sql text	PROGRAM	PQ_STATUS	blocked?	blocking session SID	EVENT
130	9755	1gpj28ptjj43p	1494045816	update emp set sal=2000	sqlplus@	ENABLED	blocked	362	enq: TX - row lock contention