RE: Unable to find SQL executed by blocking session
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "rjamya" <rjamya@xxxxxxxxx>
- Date: Fri, 15 Oct 2004 12:01:29 -0400
Glad I could help, but.....
That may not work either.
What if the blocking session does:
update tab_a set col_b=3D'Hi there' where col_a=3D1;
So, when that completes execution, you can still
see it via PREV_HASH_VALUE, right, but, now, what
if the blocker does one more statement execution:
select * from dual;
Now, the pointer to the update SQL is lost.
There is no generic solution for this.
Transactions are made up of multiple SQL
statements. There's no relationship between
locks (or enqueues) and the SQL statement
that imposed the lock. In fact, it's even
possible for something like:
update tab_a set col_b=3D'Hi there' where col_a=3D1;
and then:
alter system flush shared_pool;
Now, you'll NEVER find that statement.
It's gone from the shared pool.
Bottom line, there's no general solution to
the problem you've proprosed.
-Mark
-----Original Message-----
From: rjamya [mailto:rjamya@xxxxxxxxx]
Sent: Friday, October 15, 2004 11:54 AM
To: Bobak, Mark
Cc: sfaroult@xxxxxxxxxxxx; Oracle Discussion List
Subject: Re: Unable to find SQL executed by blocking session
Yey !!!
thanks Mark ... that was it, now I use gv$session.prev_hash_value for
the blocker session and I get what I want ....
SQL> /
** Active Locks in "RELNCS" ordered by RESOURCE **
INST RESOURCE SID S DB/OS User HOLDING
WANTING LCKTIM HASH VALUE
-------- -------------------- ----- - -------------------- -------
------- ------ ---------------
RELNCS1 TX-196612-235740 38 I TCS/ORACLEI X =20
9 0
RELNCS2 TX-196612-235740 35 A TCS/ORACLEI =20
X 6 1308698167
Resource BLOCKER "TCS/oraclei" is logged on "RELNCS1" and holding the
lock for last "9" seconds."
Partial SQL statement (hash value: 0) executed by the BLOCKER is as =
follows ...
<BLOCKER> update /* test */ raj_test set a =3D :"SYS_B_0"
Resource WAITER "TCS/oraclei" is logged on "RELNCS2" and waiting for
the lock for last "6" seconds."
Partial SQL statement (hash value: 1308698167) executed by the WAITER
is as follows ...
<WAITER> update raj_test set a =3D :"SYS_B_0"
Database lock Info, ESPN Oracle Utilities, =A9 ESPN 2004
Elapsed: 00:00:00.32=20
Thanks a bunch !!
Raj
--
//www.freelists.org/webpage/oracle-l
Other related posts:
