How to Kill the Session

How to find Table Lock – Release Table lock – Find Session ID and kill the Session?

• DBA_BLOCKERS - Shows non-waiting sessions holding locks being waited-on
• DBA_DDL_LOCKS - Shows all DDL locks held or being requested
• DBA_DML_LOCKS - Shows all DML locks held or being requested
• DBA_LOCK_INTERNAL - Displays 1 row for every lock or latch held or being requested with the username of who is holding the lock
• DBA_LOCKS - Shows all locks or latches held or being requested
• DBA_WAITERS - Shows all sessions waiting on, but not holding waited for lock

The following code can be used in order quickly identify all locked objects within your Oracle system.

Select
   C.Owner,
   C.Object_Name,
   C.Object_type,
   B.SID,
   B.Serial#,
   B.Status,
   B.Osuser,
   B.Machine
From
   v$Locked_Object A ,
   v$Session B,
   Dba_Objects C
where B.SID= A.Session_ID and A.Object_ID = C.Object_ID;

Question:  I want to know how to find the session that is holding an Oracle table lock and how to remove the lock.  Is there a script to identify the session that is holding an Oracle table row lock?

Answer:  Yes, you can query the dba_dml_locks view with the name of the Oracle table to get the system ID. 

STEP 1:  To identify the SID for the table with the lock, you will use this system ID in a later query to get the serial number for the table row lock:

Select Session_ID
From 
Dba_Dml_Locks
where Name = 'EMP_SAL';

Output:

SID
___
607

STEP 2:  The next step is a script to find the Serial# for the table row lock-

Select SID, Serial#
From 
V$Session
where SID in (Select Session_ID From Dba_Dml_Locks where Name = 'EMP_SAL');

Output:

SID SERIAL#
---- -------
607 1402

STEP 3:  Finally, we can use the "Alter System" command to kill the session that is holding the table lock:

Alter system kill session 'SID,SERIALl#';

Alter system kill session '607,1402';

Related Topics: 

ACID Property and ORACLE Table Locking - Click here for more details

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.