RE: Unable to find SQL executed by blocking session

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_at_gmail.com]
Sent: Friday, October 15, 2004 11:54 AM
To: Bobak, Mark
Cc: sfaroult_at_roughsea.com; 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

--
http://www.freelists.org/webpage/oracle-l