Find and Kill Locks in Oracle Database

Finding and resolving locks in an Oracle database involves identifying the sessions that hold locks and the sessions that are waiting for locks to be released. Here’s a step-by-step guide to finding and killing locks in an Oracle database:

1: Identify Locked Objects: First, you need to identify which objects are locked by finding the SID of WAITER and HOLDER :

SET LINESIZE 250
COL USERNAME FOR A12
COL OBJECT_NAME FOR A10
COL SID FOR A20
COL LOCK_MODE FOR A10
COL CTIME FOR A30
COL MACHINE FOR A25


SELECT S.USERNAME,
  SUBSTR(O.NAME, 1, 15) OBJECT_NAME,
  DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID) SID,
  DECODE(L.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(L.LMODE)) LOCK_MODE,
  TRUNC(L.CTIME / 3600) || ':' || TRUNC(MOD(L.CTIME, 3600) / 60) || ':' || MOD(L.CTIME, 60) CTIME,
  S.STATUS,
  S.MACHINE,
  S.SQL_ID,
  Q.SQL_TEXT
FROM (SELECT /*+ NO_MERGE */(3-LEVEL) LV,
  INST_ID,
  SID,
  TYPE,
  LMODE,
  CTIME
  FROM (SELECT /*+ NO_MERGE */A.INST_ID,
  A.SID,
  A.TYPE,
  A.LMODE,
  A.REQUEST,
  CASE
  WHEN REQUEST = 0 THEN ID1
  END ID1,
  CASE
  WHEN REQUEST > 0 THEN ID1
  END ID3,
  A.CTIME
  FROM GV$LOCK A
  WHERE A.TYPE <> 'MR') START WITH REQUEST > 0 CONNECT BY PRIOR ID3 = ID1) L,
  GV$SESSION S,
  GV$PROCESS P,
  SYS.OBJ$ O,
  GV$SQL Q
WHERE L.SID = S.SID
AND L.INST_ID = S.INST_ID
AND S.INST_ID = P.INST_ID(+)
AND S.PADDR = P.ADDR(+)
AND S.ROW_WAIT_OBJ# = O.OBJ#(+)
AND L.CTIME >= 1
AND S.SQL_ID = Q.SQL_ID(+)
GROUP BY DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID), S.INST_ID, S.USERNAME, O.NAME, L.TYPE, L.LMODE, L.CTIME, S.STATUS, S.MACHINE,S.SQL_ID,Q.SQL_TEXT;

Output:

USERNAME   OBJECT_NAME SID          LOCK_MODE     CTIME     STATUS    MACHINE               SQL_ID           SQL_TEXT
---------- ---------- ------------- ------------ ---------- --------- --------------------- ---------------- -------------------------------------------------------
SYS                    Holder: 587   Exclusive    0:8:35     INACTIVE  prod.oracle.com
SYS         TEST       Waiter: 1760  None         0:8:33     ACTIVE    prod.oracle.com       9p20hhuuf6gf8    update test set name=:"SYS_B_0" where id=:"SYS_B_1"

2: Identify SERIAL# of Holder: Now, you need to identify the Serial# against the SID of Holder:

select action, sid, serial#, seconds_in_wait , status , client_identifier, 
logon_time , module  from v$session where sid=<SID of Holder>;

Sample:
select action, sid, serial#, seconds_in_wait , status , client_identifier, 
logon_time , module  from v$session where sid=587;

Above query will return Serial# against SID.

3: Kill the Database Lock: Now we will disconnect that Holder session which will release the Lock:

alter system kill session '<SID>,<SERIAL#>';

alter system kill session '587,8743';

Now run the First query the Lock will be no longer there.

You can run the below script which will generate the DCL to kill all inactive sessions in Database:

1: Identify Number of sessions in Database:

select status,count(*) from v$session group by status;

Output:

STATUS     COUNT(*)
-------- ----------
KILLED            1
ACTIVE           77
INACTIVE          2

2: Generate DCL to kill inactive sessions:

sqlplus "/ as sysdba"

SELECT 'ALTER SYSTEM DISCONNECT SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session 
where status='INACTIVE';

Output:

'ALTERSYSTEMDISCONNECTSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
--------------------------------------------------------------------
ALTER SYSTEM DISCONNECT SESSION '1965,35441' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION '1972,1799' IMMEDIATE;

3: Run the generated DCL to kill all inactive sessions in Database:

sqlplus "/ as sysdba"

SQL> ALTER SYSTEM DISCONNECT SESSION '1965,35441' IMMEDIATE;
System altered.

SQL> ALTER SYSTEM DISCONNECT SESSION '1972,1799' IMMEDIATE;
System altered.