Blockers on the database and user sessions

session details


set linesize 200
col MACHINE format a30
col OSUSER format a10
col SCHEMANAME format a10
col MODULE format a20

Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND' and USERNAME='&a' order by logon_time asc;





Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module, INST_ID from gv$session where type !='BACKGROUND'  order by logon_time asc;





Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine,SID,module,INST_ID from gv$session where type !='BACKGROUND' and module like '%Developer' order by logon_time asc;






Blockers on the database
----------------------------
column sess format A20
SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess, id1, id2, lmode, request, type, inst_id
 FROM GV$LOCK
WHERE (id1, id2, type) IN
   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
     ORDER BY id1, request;


select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
---------------------------------------------
select b.session_id ,a.SERIAL#, a.username "Blocker Details"
from gv$session a,dba_lock b
where b.session_id = a.sid
and b.blocking_others = 'Blocking';
--------------------------------


select s.sid, s.sql_id, q.sql_text from gv$sqltext q, gv$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

---------------------------------------------------


set lines 200
set pages 1000
select inst_id,sid,serial#,sql_id,prev_sql_id,sql_hash_value,status,osuser,username,program,machine,last_call_et,to_char(logon_time,'DD/MON/YYYY hh24:mi') logon_time
from gv$session
where sid in (&sid)
order by inst_id;


---------------------------------------------------

LOCK on the database


SELECT O.OBJECT_NAME, S.SID, S.SERIAL#,P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT,S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;




select sql_id,sql_text from gv$sqlarea where sql_id='&sql_id' ;


----------------------------------------------


Query: select * from v$lock where request!=0;

select * from v$lock where type='TX' and id1='&1' and id2='&2'
               
where &1 and &2 are the ID for the lock we are waiting on from above.

V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.
In order to see locked object query,


--------------------------------------------------------

SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  owner || '.' || object_name object
,  object_type
,  decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
,  decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id
/




You can also query v$access and v$locked_object to see specific locks: 


select s.sid, s.serial#, p.spid from v$session s,v$process p where s.paddr = p.addr and s.sid in (select SESSION_ID from v$locked_object);





Blocking session for last 3 hours

SELECT  distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_serial#,a.user_id,s.sql_text,a.module
FROM  GV$ACTIVE_SESSION_HISTORY a  ,gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 
and a.sample_time > sysdate - 3/24

Blocking session for last 7 days

select * from (
SELECT a.sql_id ,
COUNT(*) OVER (PARTITION BY a.blocking_session,a.user_id ,a.program) cpt,
ROW_NUMBER() OVER (PARTITION BY a.blocking_session,a.user_id ,a.program
order by blocking_session,a.user_id ,a.program ) rn,
a.blocking_session,a.user_id ,a.program, s.sql_text
FROM sys.WRH$_ACTIVE_SESSION_HISTORY a ,sys.wrh$_sqltext s
where a.sql_id=s.sql_id
and blocking_session_serial# <> 0
and a.user_id <> 0
and a.sample_time > sysdate -1
) where rn = 1



ession details associated with Oracle SID
-------------------------------------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session  Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*'  Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program  ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) 
/

Find more details

SELECT 'Oracle Session with username'|| blocker.username  || ' and session ID '||blocker.sid
        ||' is blocking ' || blocked.username ||' with session ID '|| blocked.sid block_description
    FROM gv$lock locker, gv$session blocker, gv$lock locked, gv$session blocked
    WHERE blocker.sid=locker.sid AND blocked.sid=locked.sid
    AND locker.block=1  
    AND locker.id1 = locked.id1
    AND locker.id2 = locked.id2
    AND locker.inst_id = blocker.inst_id
    AND locked.inst_id = blocked.inst_id


SELECT inst_id,  blocking_session, sid, serial#
FROM gv$session s
WHERE blocking_session IS NOT NULL;


SELECT   s.inst_id instance_id, s.SID, s.serial#, p.username os_process_owner, p.pid, p.spid os_process_id,
         l.session_id, l.oracle_username,  l.os_user_name, l.process,
         DECODE (locked_mode,
                 0, 'None',
                 1, 'NULL',
                 2, 'Row-Share',
                 3, 'Row-Exclusive.',
                 4, 'Share',
                 5, 'S/Row-Exclusive',
                 6, 'Exclusive'
                ) AS lock_type,
         o.owner, o.object_name, o.object_type, s.program, s.logon_time, s.status session_status,
         o.status object_status
    FROM gv$locked_object l, dba_objects o, gv$session s, gv$process p
   WHERE (l.object_id = o.object_id AND l.session_id = s.SID)
     AND s.paddr = p.addr
     AND l.inst_id = p.inst_id
     AND s.inst_id = p.inst_id
ORDER BY object_name;




Complete Details of blocking sessions:

select distinct

a.sid "waiting sid"

, d.sql_text "waiting SQL"

, a.ROW_WAIT_OBJ# "locked object"

, a.BLOCKING_SESSION "blocking sid"

, c.sql_text "SQL from blocking session"

from v$session a, v$active_session_history b, v$sql c, v$sql d

where a.event='enq: TX - row lock contention'

and a.sql_id=d.sql_id

and a.blocking_session=b.session_id

and c.sql_id=b.sql_id

and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#

and b.CURRENT_FILE#= a.ROW_WAIT_FILE#

and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#

user sessions

SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A30
COLUMN machine FORMAT A20
COLUMN logon_time FORMAT A40
SELECT NVL(s.username, '(oracle)') AS username,
        s.osuser,
        s.sid,
        s.serial#,
        p.spid,
        s.status,
        s.module,
        s.machine,
        s.program,
        TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
 FROM   gv$session s,
        gv$process p
 WHERE  s.paddr = p.addr  and type !='BACKGROUND' and MODULE like 'SQL%'
 ORDER BY s.username, s.osuser;
 

The Below Query will help to get the details on Blocking sessions in RAC. Also one can get the OBJECT_NAME in next query.

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

DETAILS ON BLOCKED OBJECTS:

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' ||L1.ID1||' OBJ_NAME:'||O.OBJECT_NAME
AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2,DBA_OBJECTS O
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.ID1=OBJECT_ID from above query
AND L1.ID1=O.OBJECT_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;

Incase, User is requesting to check whether a particular TABLE is locked by any Session?, then you can use the object_id (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OWNER=’ABC’ AND OBJECT_NAME=’XYZ’) in below query.

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' ||L1.ID1||' OBJ_NAME:'||O.OBJECT_NAME
AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2,DBA_OBJECTS O
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.ID1=
AND L1.ID1=O.OBJECT_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;