Re: Unable to find SQL executed by blocking session

  • From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 15 Oct 2004 11:39:30 -0400

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

Other related posts: