Queries to check locks in oracle database

Below query to check locks in oracle database ( Single Instance)

SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type

FROM V$LOCK

WHERE (id1, id2, type) IN

(SELECT id1, id2, type FROM V$LOCK WHERE request>0)

ORDER BY id1, request

;

 

In case of RAC,below query is used check locks in oracle database

SELECT inst_id,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

;

We can also use below query to check locks

Query to find out waiting session and holding sessions in Oracle 

set linesize 1000

column waiting_session heading 'WAITING|SESSION'

column holding_session heading 'HOLDING|SESSION'

column lock_type format a15

column mode_held format a15

column mode_requested format a15

 

select

waiting_session,

holding_session,

lock_type,

mode_held,

mode_requested,

lock_id1,

lock_id2

from

dba_waiters

/

Below query can be used to find the library cache lock in single instance

select /*+ all_rows */ w1.sid waiting_session, 
h1.sid holding_session, 
w.kgllktype lock_or_pin, 
w.kgllkhdl address, 
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 
'Unknown') mode_held, 
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 
'Unknown') mode_requested 
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 
where 
(((h.kgllkmod != 0) and (h.kgllkmod != 1) 
and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) 
and 
(((w.kgllkmod = 0) or (w.kgllkmod= 1)) 
and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) 
and w.kgllktype = h.kgllktype 
and w.kgllkhdl = h.kgllkhdl 
and w.kgllkuse = w1.saddr 
and h.kgllkuse = h1.saddr 
/

Another query which can be used to see the locked objects

column sid_ser format a12 heading 'session,|serial#'; 
column username format a12 heading 'os user/|db user'; 
column process format a9 heading 'os|process'; 
column spid format a7 heading 'trace|number'; 
column owner_object format a35 heading 'owner.object'; 
column locked_mode format a13 heading 'locked|mode'; 
column status format a8 heading 'status'; 
select 
    substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser, 
    substr(l.os_user_name||'/'||l.oracle_username,1,12) username, 
    l.process, 
    p.spid, 
    substr(o.owner||'.'||o.object_name,1,35) owner_object, 
    decode(l.locked_mode, 
             1,'No Lock', 
             2,'Row Share', 
             3,'Row Exclusive', 
             4,'Share', 
             5,'Share Row Excl', 
             6,'Exclusive',null) locked_mode, 
    substr(s.status,1,8) status 
from 
    v$locked_object l, 
    all_objects     o, 
    v$session       s, 
    v$process       p 
where 
    l.object_id = o.object_id 
and l.session_id = s.sid 
and s.paddr      = p.addr 
and s.status != 'KILLED'
/

Once you have find the blocking session and decided to kill that session ,we can use below query to generate the kill session sql

select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid=&1;

Related Articles

Oracle Enqueue and locks(Oracle table locks): How it works

How to find table where statistics are locked

How to find the waitevent History of the Oracle session

Leave a Reply