Tuesday, 28 April 2015

oracle - listing locks & removing them

While logged in as sysadmin, SQL to list any locks on a particular table:
SELECT SID,SERIAL# 
FROM V$SESSION 
WHERE SID IN (SELECT SESSION_ID 
FROM DBA_DML_LOCKS 
WHERE NAME = 'SOME_TABLE_NAME');
SQL to remove a specific lock, using the values from the above SQL:
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';