Enter Your Email

Enter your email address:

Delivered by FeedBurner

Custom Search

Sunday, April 23, 2006

What is blocking at the DB Level

On web i found this script which helped me a lot :-)
***************************************************
SQL> select s1.username '@' s1.machine
2 ' ( SID=' s1.sid ' ) is blocking '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOCKING_STATUS
--------------------------------------------------------------------------------
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=32 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=36 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=45 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=49 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=72 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=86 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=90 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=99 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=123 )
NAMECH@amsig4c ( SID=88 ) is blocking KDD_WEB@amsig4c ( SID=141 )

10 rows selected.


SQL> alter system kill session '88,24356';
System altered.
SQL> select * from v$lock where block <> 0 or request <> 0;
no rows selected

************************************
Please let me know your experience in handling this issue.
 
Copyright © 2005 - 2008 DBA-ONWeb Technical blog. All rights reserved