Re: Unable to find SQL executed by blocking session

From: Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Fri, 15 Oct 2004 11:39:30 -0400
Message-Id: <416FEF32.000001.01756@DAZA-MGEJCA5J7T>

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_at_roughsea.com
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_at_gmail.com> 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

---

--
http://www.freelists.org/webpage/oracle-l[1]
 
 
 
--- Links ---
1 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists
org%2Fwebpage%2Foracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 15 2004 - 10:34:55 CDT