Lock Info in Database..Session Blocking Other Sessions..

Here are few steps with the help of this we can find the Blocking session and the Query executed by those sesson...

Step 1) Checking Blocking Session..

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
order by l1.sid;  -------[ Added later to Sort the query  ]

The Result will be like this...

SID 'ISBLOCKING' SID

---------- ------------- ----------
204 IS BLOCKING 3
387 IS BLOCKING 29
88 IS BLOCKING 38
387 IS BLOCKING 41
506 IS BLOCKING 55
387 IS BLOCKING 60
387 IS BLOCKING 80
80 IS BLOCKING 88
204 IS BLOCKING 116
387 IS BLOCKING 139
387 IS BLOCKING 174
387 IS BLOCKING 204
387 IS BLOCKING 229
204 IS BLOCKING 233
80 IS BLOCKING 245
204 IS BLOCKING 260
204 IS BLOCKING 279
204 IS BLOCKING 294
387 IS BLOCKING 310
204 IS BLOCKING 332
204 IS BLOCKING 344
387 IS BLOCKING 345
80 IS BLOCKING 359
506 IS BLOCKING 363
387 IS BLOCKING 400
387 IS BLOCKING 407
387 IS BLOCKING 412
204 IS BLOCKING 451
387 IS BLOCKING 470
204 IS BLOCKING 491
387 IS BLOCKING 506
387 IS BLOCKING 517
387 IS BLOCKING 518

33 rows selected.

Step 2 getting more  Blocking info..i.e which user bocking whome.. 
 
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
order by s1.sid;--------[  Added later for Sorting ]

You will get a result like this...to check the user info..

BLOCKING_STATUS

-------------------------------------------------------------------------------------------------------------
WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=3 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=29 )


WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=38 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=41 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=55 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=60 )


WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=70 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMSBAT@ahc055 ( SID=88 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=115 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=116 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=138 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=139 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=174 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=229 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=233 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMSBAT@ahc055 ( SID=245 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=260 )


WMSBAT@ahc055 ( SID=88 ) is blocking WMSBAT@ahc055 ( SID=261 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=279 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=294 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=310 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=332 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=344 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=345 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=80 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=359 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 ) is blocking WMSBAT@ahc055 ( SID=363 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=400 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=407 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ04 ( SID=412 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=451 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ02 ( SID=470 )


WMS_STATION@MERCURIUS\SYLOWAYDCZ01 ( SID=204 ) is blocking WMSBAT@ahc055 ( SID=491 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=506 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMS_STATION@MERCURIUS\SYLOWAYDCZ03 ( SID=517 )


WMS_STATION@EMEA\IMICTXP25 ( SID=387 ) is blocking WMSBAT@ahc055 ( SID=518 )


37 rows selected.
SQL>


 Step 3 To get the Query fired by the session's


select l.sid sid,s.username username,s.program program,t.sql_text,u.name owner,o.name object,
l.type type,lmode,
decode (lmode,1,'NULL',2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row',6,'Exclusive') mode_desc,
request,
decode (request,1,'NULL',2,'Row Share',3,'RowExclusive',4,'Share',5,'Share Row',6,'Exclusive')
request_desc
from v$lock l,
v$session s,
sys.obj$ o,
sys.user$ u,
v$sqltext t
where l.type in ('RW','TM','TX','UL')
and l.sid=s.sid(+)
and l.id1 = o.obj# (+)
and o.owner#=u.user#(+)
and s.sql_hash_value = t.hash_value
and lmode > 0;

will put the result of this ...

Vijay Kumar