Re: looking for blocking sql statement(s)

On Feb 12, 1:00 pm, Andreas Mosmann <mosm..._at_expires-29-02-2008.news- group.org> wrote:
> Mark D Powell schrieb am 12.02.2008 in
> <8db2b9cb-d0f5-4bd6-850e-54aaab86e..._at_m34g2000hsb.googlegroups.com>:
>
> > I am not sure how you tie the blocking session to the SQL statement
> > that is responsible for another session being blocked when it is not
> > the blocking sessions current SQL statement or maybe its prior
> > statement both of which can be referenced from v$session column
> > information, but I expect that it will be one of the SQL statements
> > referenced by v$open_cursor for the blocking session sid.
> > Maybe this view will be helpful.
>
> yes, it is. I tried it by updating a row by more than 1 sessions and I
> found the statement exactly there. But the statement is shortened, is
> there a method to find the whole statement?
>
> > In the case of a deadlock the ORA-00060 trace file usually contains
> > enough information to allow you to see the two SQL statements when the
> > problem is a straightforward two sessions want the same row for
> > update.
>
> I pay attention on it
>
> > HTH -- Mark D Powell --
>
> Thank you
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

[note - second attempt to post via google. first claimed to work but I could not navigate away from page so if this is a duplicate my apologies]

You should be able to find a longer version of the SQL or the full sql by joining v$open_cursor to v$sqlarea (1000 bytes) or v$sqltext (multirows  per SQL) or one of the other v$sql% views if it better suits your purpose.

HTH -- Mark D Powell -- Received on Wed Feb 13 2008 - 12:08:42 CST