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.