Hi I have this, I had one better but I don't remember where. This at least includes the kill session if you cant to kill immediatly. SELECT b.USERNAME||'(sid-ser:'||B.sid||','||B.serial#||' '||B.OSUSER||'-'||B MACHINE||'-'||TRIM(B.terminal)||')' BLOQ, C.USERNAME||'(sid-ser:'||C.sid||','||C.serial#||' '||C.OSUSER||'-'||C MACHINE||'-'||TRIM(C.terminal)||')' WAIT, u.name || '.' || o.name "OBJECT", 'LockType:'||A.LOCK_TYPE||'-ModeHeld:'||A.MODE_HELD||'-ModeReq:'||A MODE_REQUESTED "MODE", 'ALTER SYSTEM KILL SESSION '''||B.sid||','||B.serial#||''';' KILL_BLOQ, 'ALTER SYSTEM KILL SESSION '''||C.sid||','||C.serial#||''';' KILL_WAIT FROM DBA_WAITERS A,V$SESSION B,V$SESSION C, v$process p, v$_lock l1,v$lock l2, v$resource r, sys.obj$ o, sys.user$ u WHERE B.SID=A.HOLDING_SESSION AND C.SID=A.WAITING_SESSION AND B.paddr = p.addr AND O.NAME IS NOT NULL /*+ PARA QUE NO Salga doble registro */ AND B.saddr = l1.saddr AND l1.raddr = r.addr AND l2.addr = l1.laddr AND l2.type <> 'MR' AND r.id1 = o.obj# (+) AND o.owner# = u.user# (+) ; Juan Carlos Reyes Pacheco OCP -------Original Message------- From: sfaroult@xxxxxxxxxxxx Date: 10/15/04 11:38:22 To: Oracle Discussion List; rjamya Subject: Re: Unable to find SQL executed by blocking session Raj, Might it be that your sessions are attached to different instances? What are you checking? V$ or GV$ views ? Regards, Stephane Faroult RoughSea Ltd http://www.roughsea.com On Fri, 15 Oct 2004 11:18 , rjamya <rjamya@xxxxxxxxx> sent: Oracle 9204, AIX 5.2, RAC system. I am trying to write a script that will show all the blocking locks in the system and the SQL beign executed by the sessions. So, here is how I tested ... create table raj_test(a varchar2(1)) / insert into raj_Test values ('A') / commit / update /* test 1 */ raj_test set a = 'B' / in another session logging in as the same user update /* test 2 */ raj_test set a = 'C' / when i run my script, the blocker session shows hash value of 0, so I am unable to show the associated SQL. The locked session shows the hash value and I can show the sql. does anyone know why this might be happening? Any workarounds? TIA Raj ------------------------------ -- //www.freelists.org/webpage/oracle-l[1] --- Links --- 1 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists org%2Fwebpage%2Foracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l