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

Source: Internet
Author: User


Posted on:


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 - Production on Thu Jun 23 16:50:13 2011Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.Connected to:Oracle9i Enterprise Edition Release - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release - 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 ')
-- 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>
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.