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

Acidulent

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

Rasp

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. – Acidulent

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. – Rasp

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? – Acidulent

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. – Acidulent

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. – Rasp

select user from dual will not work – Spike

Answer 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); 

Siegbahn

Answer 3

Use V$SESSION instead of GV$SESSION

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

Perpend

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

@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. – Perpend

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.. 🤔 – Perpend

Answer 4

Try this (will need access to v$session and v$sql views) -

select /*My Sess*/ 'My Sess Text = hello world @ '||systimestamp SID_SR_TXT from dual
union
select 'SID = '||sid||' Serial# = '||serial# SID_SR_TXT  from v$session where sql_id in 
(select sql_id from v$sql where sql_text like '% My Sess %');

Idona

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. – Acidulent