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

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.