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).
To view sessions: In SQL Developer, click Tools, then Monitor Sessions.
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.
The Oracle/PLSQL USER function returns the user_id from the current Oracle 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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With