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. Also see these notes on
row level contention and locks.
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';
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');
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';
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
