Oracle Scratchpad

October 9, 2019


Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 12:03 pm BST Oct 9,2019

Here’s a little note that’s been hanging around as a draft since 2011 (judging by the OTN posting that prompted me to start writing it). At the time there were still plenty of people using Oracle 10g. so the question didn’t seem entirely inappropriate:

On 10g R2 when I open a sqlplus session how can I know my session SID ? I’m not DBA then can not open as sysdba and query v$session.

In all fairly recent versions of Oracle, of course, we have the option to use the sys_context() function to get the SID, but this specific option didn’t appear until some time in the 10g timeline – so you might have spent years “knowing” that you could get the audsid (using sys_context(‘userenv’,’sessionid’) ) but not the sid. Now, of course, and even in the timeline of the original posting, the simplest solution to the requirement is to execute:

select sys_context('userenv','sid') from dual;

But there are a number of alternatives that may occasionally do a better job (and sometimes are just plain silly). It’s also worth noting that even in 19c Oracle still doesn’t have access to v$session.serial# through sys_context() and, anyway, sys_context() behaves like an unpeekable bind variable – which can be a problem.

So here’s the first of several options:

select sid from V$mystat where rownum = 1;

You’ll need SYS to grant you select on v_$mystat to use this one, of course, but v$mystat is a very convenient view giving you the session activity stats since logon for your own session – so there ought to be some mechanism in place that allows you to see some form of it anyway (ideally including the join to v$statname).

One of the oldest ways of getting access to your session ID without having access to any of the dynamic performance views was through the dbms_support package:

variable v1 varchar2(32)
execute :v1 := dbms_support.mysid
execute dbms_output.put_line(:v1)

Again you’ll need SYS to grant you extra privileges, in this case execute on the dbms_support package – worse still, the package is not installed by default. In fact (after installing it) if you call dbms_support.package_version it returns the value: “DBMS_SUPPORT Version 1.0 (17-Aug-1998) – Requires Oracle 7.2 – 8.0.5” – which gives you some idea of how old it is. It used to be useful for the start_trace_in_session() procedure it contains but that procedure has been superseded by many newer mechanisms. If you enable SQL tracing to see what’s happening under the covers when you call dbms_support.mysid you’ll see that the function actually runs the query against v$mystat that I showed earlier on.

Unlike dbms_support the dbms_session package is installed automatically and the privilege to execute it is granted to public,  and this gives you a function to generate a “unique session id” . The notes in the scripts $ORACLE_HOME/rdbms/admin/dbmssess.sql that create the package say that the return value can be up to 24 bytes long, but so far the maximum I’ve seen is 12 – maybe there’s some allowance for RAC instance names in there.

select dbms_session.unique_session_id from dual;

        to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') sid,
        to_number(substr(dbms_session.unique_session_id,5,4),'XXXX') serial#,
        to_number(substr(dbms_session.unique_session_id,9,4),'XXXX') instance

---------- ---------- --------
       255      24472        1

As you can see, the session_unique_id can be decoded to produce three useful bits of information, and the nice thing about this call is that it gives you session serial# at the same time as the SID. It’s possible, of course, that this query is as efficient as it could be, but there’s some scope for writing a query that uses a non-mergeable in-line view to call the function once, then splits the result into three pieces.

While we’re on the session_unique_id, the dbms_pipe package also has a “unique identifier” function unique_session_name():

SQL> select dbms_pipe.unique_session_name from dual;


It doesn’t take a lot of effort to spot that the “unique session name” is the “unique session id” of dbms_session prefixed with the text “ORA$PIPE$”. It’s convenient for the dbms_pipe package to be able to generate a unique name so that one session can create a safely named pipe and tell another session about it, so anyone using pipes should take advantage of this function for its original purpose. Unlike dbms_session, though, you’ll need to be granted the privilege to execute this package; it’s not available to public. Interestingly the script that creates dbms_pipe says that this function could return 30 bytes – since it seems to be 9 bytes prepended to the (“could be 24 bytes”) dbms_session.unique_session_id you have to wonder whether there’s something more subtle that could happen or whether the notes are just out of date and irrelevant.

There may be many more mechanisms available as built-ins but the last one I know of is in the dbms_debug_jdwp package (another package with execute privilege already granted to public and the ability to supply both the sid and serial#):

SQL> select
  2          dbms_debug_jdwp.current_session_id     sid,
  3          dbms_debug_jdwp.current_session_serial serial#
  4  from dual
  5  /

       SID    SERIAL#
---------- ----------
       255      24472

There is a reason why I’ve decided to resurrect this list of ways of getting at a session’s SID, but that’s the topic of another blog note.


You need to be a little careful with Oracle’s concept of a unique_session_id” – it is unique across current sessions and instances, and it may be unique for quite a long time in the lifetime of an instance, but the serial# is a two-byte value in x$ksuse, which means it has to recycle after reaching 65535 – which may be a lot of logon/logoff sessions for a single sid to get through – but it does happen. More simplistically, serial#  values are not persisted across database restarts, so you’re much more likely to generate the same “unique_session_id” if you restart the instance fairly frequently.

(Once upon a time I think the serial# for all sessions would restart at 1 when the instance restarted, but that’s not the case any more.)







  1. The shortest one I’ve found is
    select userenv(‘sid’) from dual;

    Comment by Viacheslav Andzhich — October 9, 2019 @ 2:56 pm BST Oct 9,2019 | Reply

    • Viacheslav Andzhich,

      True – I shouldn’t have left that one out.

      Even though userenv() was deprecated by 10g (not that that stops Oracle using it internally, even in 19c, in the v$/x$ conversions).

      Jonathan Lewis

      Comment by Jonathan Lewis — October 9, 2019 @ 2:59 pm BST Oct 9,2019 | Reply

  2. […] What’s my SID (Oct 2019): several ways of getting the SID (and some for the serial#) […]

    Pingback by Infrastructure Catalogue | Oracle Scratchpad — March 13, 2022 @ 11:40 am GMT Mar 13,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: