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

[Doron]

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

[sybrandb]

do*********@msn.com (Doron) wrote in message news:<99**************************@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