Queries on Locks and Blocks

Blocking session details from GVLock:

set lines 1000
select l1.sid || ' inst#'|| l1.INST_ID||' is blocking ' || l2.sid|| ' inst#'||l2.inst_id from gv$lock l1 , gv$lock l2
where l1.block > 0 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

Blocking session details from GVSession:

set lines 200 pages 100

col blocking_session for a15

select

   blocking_session,

   sid,

   serial#,

   wait_class,

   seconds_in_wait

from

   gv$session

where

   blocking_session is not NULL

order by

   blocking_session

/

Blocking session details from GVSession and GVLock:

set lines 2000 pages 100

col blocking_session for a15

col program for a40

col username for a15

col event for a40

col module for a40

col action for a40

col owner for a40

col object_name for a40

col osuser for a40

col logon_time for a40

SELECT b.inst_id,

       CASE WHEN A.request != 0 THEN '-> '||a.sid|| ' (BLOCKED)' ELSE TO_CHAR(a.sid) END sid,

       a.id1,

       a.id2,

       CASE a.lmode

       WHEN 0 THEN a.lmode||' - None'

       WHEN 1 THEN a.lmode||' - null'

       WHEN 2 THEN a.lmode||' - row-S'

       WHEN 3 THEN a.lmode||' - row-X'

       WHEN 4 THEN a.lmode||' - share'

       WHEN 5 THEN a.lmode||' - S/Row-X'

       WHEN 6 THEN a.lmode||' - exclusive'

       ELSE a.lmode||' - Unknown'

       END Lock_mode,

       CASE a.block

       WHEN 0 THEN a.block||' - Not blocking'

       ELSE a.block||' - Blocking'

       END block_type,

       CASE a.request

       WHEN 0 THEN a.request||' - None'

       WHEN 1 THEN a.request||' - null'

       WHEN 2 THEN a.request||' - row-S'

       WHEN 3 THEN a.request||' - row-X'

       WHEN 4 THEN a.request||' - share'

       WHEN 5 THEN a.request||' - S/Row-X'

       WHEN 6 THEN a.request||' - exclusive'

       ELSE a.request||' - Unknown'

       END request,

       b.blocking_session,

       b.blocking_instance,

       b.blocking_session_status,

       b.program,

       b.username,

       b.status,

       b.event,

       b.module,

       b.action,

       o.owner,

       o.object_name,

       b.osuser,

       to_char(b.logon_time,'DD/MON/YY HH24:MI:SS') logon_time

 FROM gv$lock a,

      gv$session b,

       dba_objects o

 WHERE (a.id1, a.id2) IN (SELECT id1, id2

                            FROM gv$lock

                           WHERE lmode = 0)

   AND a.inst_id = b.inst_id

   AND a.sid = b.sid

   AND o.object_id(+) = b.row_wait_obj#

 ORDER BY a.id1, a.id2, a.request;

Other details from GVSession and GVLock:

set lines 2000 pages 100

col username format A15

col SESSION for a15

col machine for a75

col program for a75

select

ses.inst_id,

ses.sid||','||ses.serial# "SESSION",

proc.spid,

ses.action,

ses.module,

ses.status,

to_char(ses.logon_time, 'MM/DD/YYYY HH24:MI:SS') LOGIN_TIME,

ses.username,

ses.client_identifier clienid,

ses.machine,

ses.program,

ses.sql_id,

ses.last_call_et,

ses.event,

ses.state,

ses.wait_time,

ses.seconds_in_wait

from gv$session ses , gv$process proc

where ses.paddr = proc.addr

and ses.inst_id = proc.inst_id

and exists (select block from gv$lock lck where lck.inst_id=ses.inst_id and lck.sid=ses.sid and lck.block=1)

order by ses.logon_time

/