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@xxxxxxxxxxxxxxx> 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

Other related posts: