Re: Unable to find SQL executed by blocking session

Thanks again Mark,

after sending the last message, I was writing the code and similar thoughts came to me and a quick test proved that it was wise not to use hash_value or prev_hash_value to find the blocking sql. So, i removed that check from my code.  

Thanks for writing ... much appreciated.

Raj

On Fri, 15 Oct 2004 12:01:29 -0400, Bobak, Mark <mark.bobak_at_il.proquest.com> wrote:
> Glad I could help, but.....
>
> That may not work either.
>
> What if the blocking session does:
> update tab_a set col_b='Hi there' where col_a=1;
>
> 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='Hi there' where col_a=1;
>
> 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

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