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  Oracle 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/how to check lock on package in oracle

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 ( how to find locked tables in oracle)

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;

How to  unlock table in  Oracle 

First find the session which are holding locks on the table using the above query and then kill the sessions using alter system kill session

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