By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,501 Members | 1,859 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,501 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.