Re: How to get the SQL query of a blocking sid during a lock contention

Try prev_hash_value. sql_hash_value is 0 when the sql is already completed.

I am suspecting the blocker is also waiting for something, or the hash_value shouldn't be 0.

You can find out what the blocker is doing from v$session_wait.

Another way to find out sql_text is via sql_address. Use this script see if it makes any difference:

column addr new_value addr;
select sql_address addr
from v$session
where sid=&sid;

select sql_text from
sys.v_$sqltext
where address = hextoraw('&addr')
order by piece; Received on Tue Jul 12 2005 - 14:30:55 CDT