Checking Blocking session information

 

--> Check session waiting for lock 

SELECT  event , 

      row_wait_obj#       AS object,

       row_wait_file#      AS datafile,

       row_wait_block#     AS datablock,

       row_wait_row#       AS rowinfo

  FROM gv$session

where event  like 'enq%'  ;

--  WHERE event='enq: TX - row lock contention'

--   AND state='WAITING' ;

--> Session Holding Lock 

SELECT gvs.inst_id,DECODE (request, 0, 'Holder: ', 'waiter:') || gvl.sid     sess,

         status,

         id1,

         id2,

         lmode,

         request,

         gvl.TYPE

    FROM gv$lock gvl, gv$session gvs

   WHERE     (id1, id2, gvl.TYPE) IN (SELECT id1, id2, TYPE

                                        FROM gv$lock

                                       WHERE request > 0)

         AND gvl.sid = gvs.sid and gvl.inst_id=gvs.inst_id

ORDER BY id1, request;

--> Find Sql id  executed by Blocking  sessions 

SQL> select SADDR from gv$session where sid=2566 and inst_id=2;

SQL> select inst_id,sid,sql_id,sql_text,LAST_SQL_ACTIVE_TIME, CURSOR_TYPE from gv$open_cursor  where saddr='0000000290D35400' and inst_id=2 ;

SQL> select sql_fulltext as sql_statement from gv$sql where sql_id='czyw4auh9z073';

Reference :

How to Diagnose Issues Where ‘enq: TX – row lock contention’ Waits are Occurring (Doc ID 1986832.1) , Resolving Issues Where ‘enq: TX – row lock contention’ Waits are Occurring (Doc ID 1476298.1) 

WAITEVENT: “enq: TX – row lock contention” Reference Note (Doc ID 1966048.1)