Friday, July 9, 2010

Finding blocked sessions in oracle

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

select SID||','||SERIAL# from v$session where sid = 20

alter system kill session '20,35205'

2 comments:

hasika said...
This comment has been removed by the author.
Anonymous said...

does this queries can run on Oracle Database 9i??

because when I am googling, and I found http://appsdbanew.wordpress.com/2007/11/05/how-to-find-blocking-session-and-kill-the-session-from-database/

their query only can be run on Oracle DB 10, can't be run on Oracle db 9 :(