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

From: <kaparwan_at_gmail.com>
Date: Fri, 9 Jan 2009 11:15:44 -0800 (PST)
Message-ID: <fc3c0500-3b5d-4379-9649-31fc3880fcc8_at_w35g2000yqm.googlegroups.com>

On Jan 9, 4:58 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> l0..._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?- Hide quoted text -
>
> - Show quoted text -

You cannot get it. No way.
blocker is gone away. Received on Fri Jan 09 2009 - 13:15:44 CST