Identifying all SQL generated by a specific session

Tags:
Oracle
I am trying to isolate all SQL generated by a specific user in a specific session. Short of using auditing can I tie specific session parameters like sid, serial#, username to SQL still available in the shared SQL area?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can get the information of a specific session from v$session and v$open_cursor views, like in this example:

select sid, user_name, sql_text, address
from v$open_cursor
where sid in (
select v_s.sid
from v$session v_s
where v_s.program like ‘%LadProc%’)
and sql_text like ‘SELECT%’
order by sid,address

Here i’m looking for all currently active select statements of a specific Process “LadProc”.
You can also filter on v$session.username.
I hope this can help.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • PRASAD55
    one can also use OEM to check what query a particular session is running.
    0 pointsBadges:
    report
  • Pegasusdba
    Unfortunately, when I run the query provided by Mysterky, the sql code is limited to 60 characters. The sql_text column in v$open_cursor is varchar2(60). How does one get the complete code?
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: