SQL Query to Get Oracle Blocking Sessions

In this post I am going to explain you how to get the Oracle blocking session details. 

SQL 

Get details of which session is blocking and waiting.

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;

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
HOLDER: 18707                                     367036186          9          6          0 DW
HOLDER: 4853                                      367036186          5          6          0 DW
WAITER: 16071                                     367036186          5          0          4 DW
WAITER: 17852                                     367036186          9          0          4 DW
WAITER: 8806                                      367036186          9          0          4 DW

advertisements

 

col PROGRAM for a35
col MODULE for a40
col inst_id for 9999
col EVENT for a30
col sid for 999999
Set pages 1000 lines 222
select   distinct S.INST_ID, sid,  s.event, s.status, s.program, s.module, s.BLOCKING_SESSION BLK_Sess, q.sql_ID from    gv$session s,    gv$sql q
where    s.state ='WAITING'
and      wait_class != 'Idle'   and  q.sql_id = s.sql_id
and      (s.sid,s.inst_id) in (SELECT  SID,inst_id 
FROM GV$LOCK WHERE (ID1, ID2, TYPE) IN(SELECT ID1,ID2, TYPE FROM GV$LOCK WHERE REQUEST>0 ));


INST_ID     SID EVENT                          STATUS   PROGRAM                             MODULE                                               BLK_SESS SQL_ID
------- ------- ------------------------------ -------- ----------------------------------- -------------------------------------------------- ---------- -------------
      1    3822 enq: TX - row lock contention  ACTIVE   Payroll    finance         4564 4vx7rq028ho5w
      1    2093 enq: TX - row lock contention  ACTIVE   Payroll    finance         4564 4vx7rq028ho5w
      1    1597 enq: TX - row lock contention  ACTIVE   Payroll    finance         4564 4vx7rq028ho5w