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

Question

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).

Solution 1

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.

Solution 2

Joining gv$session and v$mystat :)

 SELECT SID, SERIAL#,inst_id
  FROM GV$SESSION
 WHERE sid=(select sid from v$mystat where rownum=1); 

Comments

[Volodymyr Frolov]

Unfortunately both options cannot be used in my case (I've explicitly mentioned that in my question). AUDSID plus SID is not unique in case of Oracle RAC (only combination of SID and SERIAL# is unique) and V$MYSTAT is unavailable because statistics is turned off.

[Mark J. Bobak]

I missed the comment about V$MYSTAT, sorry. BTW, I don't think it's true. I haven't tested, but I suspect that V$MYSTAT is always present. But I don't see anything about not being able to use V$SESSION instead of GV$SESSION. However, if you really can't use V$SESSION for some crazy reason, then use the query I have above, but change it backto GV$, and then add one additional predicate, "and inst_id = userenv('Instance')". That's the same predicate Oracle uses in the definition of a V$ view.

[Volodymyr Frolov]

As I've said above, both V$SESSION and GV$SESSION may yield more than one row in Oracle RAC configuration, when filtered by only AUDSID and even by combination of AUDSID plus SID. AUDSID may not be unique. Only combination of SID plus SERIAL# is unique, but how to get SERIAL# of current session?

[Volodymyr Frolov]

But inst_id = userenv('Instance') may do the trick. Could you please add it as a separate answer? I'll test it and mark as correct if it works.

[Mark J. Bobak]

I edited the answer above, as, when I went to add a new answer, the system strongly encouraged me to edit and improve my existing answer, rather than add a new answer.

[Volodymyr Frolov]

This is not going to work if there's some other session which tries to find itself in the same manner. Instead of "My Sess" there must be some unique identifier.

[Volodymyr Frolov]

V$SESSION in not producing unique SID and SERIAL# in case of RAC installation

[GolezTrol]

@VolodymyrFrolov The other way around. GV$SESSION gives you all sessions of every node, and SID can be not unique in that view. V$SESSION gives you only the sessions of your current node, and in that context the SID is unique, so if you query your session id from that view (Sys_Context('USERENV', 'SID')), you will get your session, and not accidentally somebody else's session that happens to have the same SID.

[GolezTrol]

See also RAC V$ vs GV$ views That said, I'm not sure why I added this answer three years after Mark J. Bobak added his, which has the same information and more.. 🤔