Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there any way to get information about current session from gv$session in oracle?

Is there any way to uniquely identify current session in GV$SESSION view in Oracle?

I've faced with the problem that the following query may return more than one row in case of Oracle RAC configuration:

SELECT SID, SERIAL#
FROM GV$SESSION
WHERE AUDSID = Sys_Context('USERENV', 'SESSIONID')
   AND SID = Sys_Context('USERENV', 'SID');

Using V$MYSTAT is not an option either, because V$MYSTAT may not be accessible for the current session (for example when statistic is disabled).

like image 423
Volodymyr Frolov Avatar asked Mar 02 '15 16:03

Volodymyr Frolov


People also ask

How do you check what session is doing in Oracle?

To view sessions: In SQL Developer, click Tools, then Monitor Sessions.

What is Active session History in Oracle?

It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn't belong to the Idle wait class.

Which function is used to get the user name of the current session in Oracle?

The Oracle/PLSQL USER function returns the user_id from the current Oracle session.

What is the difference between GV session and V session?

V$ views contain statistics for one instance, whereas GV$ views contain information from all the active instances. Each GV$ view contains an INST_ID column of type NUMBER, which can be used to identify the instance associated with the row data.


1 Answers

Try this:

SELECT SID, SERIAL#
FROM V$SESSION
WHERE AUDSID = Sys_Context('USERENV', 'SESSIONID');

Since you're interested in current session, the current session must be on the local instance (by definition), so use V$SESSION instead of GV$SESSION. Also, all you need is AUDSID to uniquely identify your session.

If you've got some reason you really need to use GV$SESSION (can't imagine why that would be), you could do this instead:

SELECT SID, SERIAL#
    FROM GV$SESSION
    WHERE AUDSID = Sys_Context('USERENV', 'SESSIONID')
      AND INST_ID = USERENV('Instance');

Also, an alternate way to get the SID of the current session is:

select sid from v$mystat where rownum=1;

Hope that helps.

like image 167
Mark J. Bobak Avatar answered Sep 28 '22 19:09

Mark J. Bobak