Oracle obtains the current session (process) session ID (SID)

 

Posted on: http://www.idb-stock.net/idb/2011/06/23/184.html

 

Session IDs are very useful in some auditing and statistical analysis. This article describes common methods to obtain the Oracle session ID:

1. Obtain the session ID (SID) of the current Oracle session from the V $ mystat View ): View plain Copy to clipboard Print ?

1. SelectSid
   
2. FromV $ mystat
   
3. WhereRownum = 1;
   

select sid  from v$mystat where rownum = 1;

2. Use the userenv function to obtain the session ID (SID) of the current Oracle session ): View plain Copy to clipboard Print ?

1. SelectSid
   
2. FromV $ session
   
3. WhereAudsid = userenv ('sessionid ');
   

select sidfrom v$sessionwhere audsid=userenv('sessionid');

Note: userenv ('sessionid') imposes restrictions on users logging on to SYS. We can see that the audsid of all users logging on to the host using sys is 0.

$ sqlplus "/as sysdba"SQL*Plus: Release 9.2.0.7.0 - Production on Thu Jun 23 16:50:13 2011Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.7.0 - ProductionSQL> show userUSER is "SYS"SQL> select t.SID, 2 t.AUDSID, 3 t.USERNAME 4 from v$session t 5 where t.USERNAME = 'SYS' 6 or t.USERNAME is null 7 ; SID AUDSID USERNAME---------- ---------- ------------------------------ 1 0 2 0 3 0 4 0 5 0 6 0 7 0 8 0 27 0 SYS9 rows selected.SQL> select userenv('sessionid') from dual;USERENV('SESSIONID')-------------------- 0

3. Use userenv ('sid ') to obtain the session ID (SID) for Oracle 10g ): View plain Copy to clipboard Print ?

1. -- Oracle can be used after 10 GB
   
2. SelectUserenv ('sid ')
   FromDual;
   

-- Oracle 10 Gb later can use select userenv ('sid ') from dual;

4. Use dbms_session.unique_session_id to obtain the session ID (SID) of the current Oracle session ):

Dbms_session.unique_session_id returns a total of 20 bits, the first four of which are session IDs, 5 ~ The 8-bit is serial #. For specific examples, see:Count the transactions of each Oracle user

View plain Copy to clipboard Print ?

1. Select
   
2. To_number (substrb (dbms_session.unique_session_id,), 'xxx') Sid,
   
3. To_number (substrb (dbms_session.unique_session_id, 5, 4), 'xxx') audsid
   
4. FromDual;
   

selectto_number(substrb(dbms_session.unique_session_id,1,4),'xxxx') sid,to_number(substrb(dbms_session.unique_session_id,5,4),'xxxx') audsidfrom dual;

SQL> select 2 to_number(substrb(dbms_session.unique_session_id,1,4),'xxxx') sid, 3 to_number(substrb(dbms_session.unique_session_id,5,4),'xxxx') audsid 4 from dual; SID AUDSID---------- ---------- 27 7651SQL>