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
?
- SelectSid
- FromV $ mystat
- 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
?
- SelectSid
- FromV $ session
- 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
?
- -- Oracle can be used after 10 GB
- 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
?
- Select
- To_number (substrb (dbms_session.unique_session_id,), 'xxx') Sid,
- To_number (substrb (dbms_session.unique_session_id, 5, 4), 'xxx') audsid
- 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> |