Re: get the sql producing lock

Michel Cadot wrote:
> <cnwy_at_263.net> a écrit dans le message de news: 1142773921.616585.170900_at_u72g2000cwu.googlegroups.com...
> | Hello,
> | Is there a simple way to get the sql statement which locked the
> | object in a transaction?
> |
> |
> | Thanks.
> |
> | wy.
> |
>
> Join v$locked_object, v$session and v$sqltext/v$sqlarea.
>
> Regards
> Michel Cadot

That would only be correct if the session that locked the object is still doing the very same sql at the time of query. In reality, it may very well be doing something unrelated now, or may be totally idle (i.e. will not show any sql as a result of this join). In fact, the actual sql that locked the object may very well be flushed out of shared pool by now, and not even available anywhere.

The answer, in general, there is no reliable way to get this information.

You may be able to figure out what kind of operation it was by examining log miner information for the locking session. However, that would not give you the original sql. Received on Sun Mar 19 2006 - 10:09:16 CST