How to get the 'SQL query' which is blocking rows

From: <l0ll1_at_yahoo.com>
Date: Fri, 9 Jan 2009 07:44:29 -0800 (PST)
Message-ID: <20cd350f-f96e-4d40-9fc9-37b2ee1c1ccd_at_q30g2000prq.googlegroups.com>



Hello All,

I was able to get to the object using object_id from v$lock.p1 for TM type lock. But I actually want the sql query from blocking session which locked that objects row(s). For example:

session 1:
update table_a ..or select for update.. or delete ... select * from table_b ..
select * from table_c ...

session 2:
update table_a...

At this time, if we run your query on v$sql for blocking session, sql_hash_value or prev_hash_value will return select * .. statements. But, I wanted to get to the update statement.

What are my options here? Using blockers SID can we get all the sql queries issued by that blocking session from ASH then locate sql issuing locks (update/select for update/delete)??? Does any ASH views store sid along with sql queries?

thanks,
Uday Received on Fri Jan 09 2009 - 09:44:29 CST

Original text of this message