Display Oracle Session ID (SID)


Problem

Finding Oracle Session ID (SID)

The current Oracle session ID is often used when you want to analyze data from v$session and similar views.


Solution

Recipe #1 - Finding the Oracle Session ID

There are several ways to get the Oracle session ID, here's the probably most used version:

SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL;

Recipe #2 - combining Session ID and v$session

Here's a simple example showing you how to use the current session ID in combination with v$session:

SELECT osuser, program
FROM v$session 
WHERE sid=SYS_CONTEXT('USERENV', 'SID');

Recipe #3 - alternative ways to get session ID

just in case you wonder, there are more ways to get the current session ID:

SELECT sid
FROM v$mystat 
WHERE ROWNUM = 1;

You could also use DISTINCT, GROUP BY among a few other commands to get just a single row instead of ROWNUM but that's doesn't really change anything.

If you want to use something more complicated, go for this:

SELECT
TO_NUMBER(SUBSTR(DBMS_SESSION.unique_session_id, 1, 4)) sid
FROM dual;

Comments




Please sign-in to post a comment