Thursday, July 16, 2009

Query to Check Database Locks and Kill the locked sessions

Below query lists all the objects that have been locked:
SELECT s.SID
,s.serial#
,SUBSTR (s.module, 1, 20) module
,s.status
,lo.os_user_name
,o.object_name
,l.lmode
,l.TYPE lock_type
FROM v$lock l, v$session s, all_objects o, v$locked_object lo
WHERE l.SID = s.SID
AND s.SID = lo.session_id
AND lo.object_id = o.object_id
--AND o.object_name LIKE 'TTWIPJOBCLNUP_NEW'
--AND o.object_type = 'PACKAGE'
--AND o.owner = 'IGS'
ORDER BY o.object_name

Use the below given Command to KILL the particular Session:
--ALTER SYSTEM KILL SESSION 'SID,SERIAL#'
ALTER SYSTEM KILL SESSION '1946,3897'

No comments:

Post a Comment