Monday, February 14, 2011

find the Locked objects and Kill the sessions

Find the Locked objects

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.osuser
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

Query to kill the session

SELECT 'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||''''||';'
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
and S.USERNAME ='SCOTT';

No comments:

Post a Comment