3

We have application id ( assume APP_ID ) to connect to database and use dbms_session.set_context() to set the actual logged on user ( ex:user1,user2) for VPD.

For performance issues in queries, we want to identify the user running it ( user1, user2). Is there any way to get dbms_session.get_context() of a connection by supplying the sid?

  1. Assume DBA can run it.
  2. I want to dump the actual userid from the context and its connection id (sid)
  3. Oracle database

I know that application login or dba tracing or a procedure to log information can be created... I just want to know, is there any out of the box way?

Durandal
  • 5,575
  • 5
  • 35
  • 49
user3339205
  • 31
  • 3

1 Answers1

0

If you're having a DBA run it you should be able to get access to the V$SESSION view. A simple query like this will give you all connected sids & their usernames:

select
       substr(sid,1,5) sid,
       substr(serial#,1,5) ser_num,
       substr(machine,1,6) box,
       substr(username,1,10) username,
       substr(osuser,1,8) os_user,
       substr(program,1,30) program
from v$session
where
and type='USER'
order by username;

If you're just looking for the actual running queries and the user/sid you could run something like described here in this answer.

Community
  • 1
  • 1
Durandal
  • 5,575
  • 5
  • 35
  • 49