Martin Widlake's Yet Another Oracle Blog

Who am I? {What is my SID} June 18, 2010

Yesterday, I posted about what AUDSID is. One of the comments, from Gary Myers, included the info that you can get AUDSID via sys_context – “select sys_context(‘USERENV’,’SESSIONID’) from dual;”.

In one of those wonderous little quirks of fate, it was the need to get my sesssion’s current SID and the use of “select sys_context(‘USERENV’,’SESSIONID’) from dual;” that reminded me of AUDSID and my intention to say something about it. SESSIONID from that little select is the AUDSID and you then check V$SESSION for the SID of the record with that AUDSID. I guess that works so long as you are NOT logged on as SYS (see previous email for why). See the script below for an example, in the commented out section.

Anyway, back to the nominal topic of getting your SID. Lots of people have posted about it before, but let not plagiarism stop me. I actually tend to use v$mystat myself. From my big book of little scripts I drag from site to site:

-- my_sid.sql
-- Martin Widlake 09/06/08
-- Get my current SID
-- I can never remember the syntax
set pages 32
set pause on
spool my_sid.lst
select sid
from v$mystat
where rownum < 2
/
--or
--select sys_context('USERENV','SID') 
--from dual
--/
-- or
-- SELECT sid 
-- FROM V$SESSION
-- WHERE audsid = SYS_CONTEXT('userenv','sessionid');
--/
spool off
clear col
--
-- EOF
--

>@my_sid

      SID
---------
      530

Boy does it take me a lot of words and even a 31-line output file to say:

select sid from v$mystat where rownum < 2;

I do wish, like many, that you could simple say “select sid from dual;” in the same way as “select user from dual”, but it the great scheme of things it does not keep me awake at night.