Re: assotiating SQL text from the V$%SQL% views with sessions

doron_almog_at_msn.com (Doron) wrote in message news:<995517bc.0401131438.26d1ba68_at_posting.google.com>...
> Hi,
> Is there any way to associate SQL text from the V$%SQL% views with
> sessions.
>
> A piece of code in our application locks up a critical table about
> once a month. I suspect that the code is not committing one or more
> transactions on that table.
>
> I can find out which session is locking that specific table and I can
> detect the locking of the table about 2 min after it happens. I tried
> looking at V$%SQL% views but couldn't associate code with sessions.
>
> I'm not permitted to trace changes to that specific table (or any
> other table).
>
> Is there a way of associating code with SID or time/timestamp via the
> V$%SQL% views (or any other views).
>
> Thanks,
> Doron

hash_value and address appear in v$open_cursor. V$open_cursor links to v$session. If the statement is current sql_address and sql_hash_value of v$session point to v$sqlarea, v$sqltext etc.

To 'trace changes' you could of course use dbms_logmnr. No extra overhead associated!

Hth

Sybrand Bakker
Senior Oracle DBA Received on Wed Jan 14 2004 - 02:53:13 CST