Re: How to get the 'SQL query' which is blocking rows
From: Gerard H. Pille <ghp_at_skynet.be>
Date: Fri, 09 Jan 2009 17:58:16 +0100
Message-ID: <49678227$0$2850$ba620e4c_at_news.skynet.be>
l0ll1_at_yahoo.com schreef:
> 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
Date: Fri, 09 Jan 2009 17:58:16 +0100
Message-ID: <49678227$0$2850$ba620e4c_at_news.skynet.be>
l0ll1_at_yahoo.com schreef:
> 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
check the open cursors of the blocker? Received on Fri Jan 09 2009 - 10:58:16 CST