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
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:
name = 'EMP';
STEP 2: The next step is a script
to find the Serial# for the table row lock :
name = 'EMP')
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
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
for 30% off directly from the publisher.