Actually (one node RAC) I can identify a session in V_$SESSION with AUDSID = "sys_context('USERENV', 'SESSIONID')".
We are switching to multiple node RAC and I have to use GV_$SESSION and DBMS_SESSION.UNIQUE_SESSION_ID to get a unique id across all nodes.
But I didn't found how to join on GV_$SESSION (select * from GV_$SESSION where ??? = unique_session_id).
I need it to retrieve OS username of a session, I'm using DBMS_SESSION.UNIQUE_SESSION_ID for application lock mechanism.
Tips are welcome.
To get a current session across RAC nodes, filter gv$session on inst_id as well:
select sid, serial# from gv$session
where audsid = sys_context('USERENV', 'SESSIONID')
and inst_id = sys_context('USERENV', 'INSTANCE');
SID SERIAL#
100 15,850
You can extract these out of dbms_session.unique_session_id:
select sid, serial#, inst_id,
to_number(substr(dbms_session.unique_session_id,1,4),'XXXXXXXXX' ) usid,
to_number(substr(dbms_session.unique_session_id,5,4),'XXXXXXXXX' ) userial,
to_number(substr(dbms_session.unique_session_id,9,4),'XXXXXXXXX' ) uinst
from gv$session
where audsid = sys_context('USERENV', 'SESSIONID')
and inst_id = sys_context('USERENV', 'INSTANCE');
SID SERIAL# INST_ID USID USERIAL UINST
100 15,850 1 100 15,850 1
But this isn't officially documented! So I wouldn't rely on it.