Script to check locks in oracle database

In this post, we will see the Script to check locks in oracle database, how to check locks in oracle, how to check lock on table in oracle

Locks is a process in the Oracle database through which it maintains consistency and concurrency. So they keep coming and going. We generally don’t need to worry about them. Sometimes we may still have sessions waiting on locks for a long time and not completing, so in that case, we will need to find the locks and then resolve it by taking proper action.
The scripts below work only for enqueue type locks. If it is library cache lock or pin, it will not show any results

Table of Contents

Script to check locks in oracle database ( Single Instance)

We can use the below query to find the holder and waiter in the Database. It will show all the sessions waiting on the enqueue wait event.

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
;

Or 
SELECT
l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions
FROM
v$lock l1, v$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2;

In the case of RAC, the 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 the 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
/

Query to find locked objects in oracle / how to find locked tables in oracle

The below query can be used to find all the 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'
/

How to check lock on table in oracle

We can use the below query to find the lock on a particular table

SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM 
    v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND c.object_name LIKE '%' || upper('&tab_name') || '%'
AND nvl(vs.status
      ,'XX') != 'KILLED';

Once you have found the blocking session and decided to kill oracle session,we can use the 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