oracle获取当前会话(进程)session id(sid)

 

转帖于：http://www.idb-stock.net/idb/2011/06/23/184.html

 

session id 在一些审计、统计分析中还是非常用的，本文介绍常见的获取oracle session id的方法：

1.从v$mystat视图获取oracle当前会话的session id(sid):

view plaincopy to clipboardprint?

1. select sid
2. from v$mystat
3. where rownum = 1;

select sid  from v$mystat where rownum = 1;

2.用userenv函数获取oracle当前会话的session id(sid):

view plaincopy to clipboardprint?

1. select sid
2. from v$session
3. where audsid=userenv('sessionid');

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

特别说明：userenv(‘sessionid’)对以sys登录的用户是有限制，我们可以看到所有在主机以sys登录的用户的audsid都是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.oracle 10g 用userenv(‘sid’)获取session id(sid):

view plaincopy to clipboardprint?

1. --oracle 10g以后可以使用
2. select userenv('sid')from dual;

--oracle 10g以后可以使用select userenv('sid') from dual;

4.使用dbms_session.unique_session_id获取oracle当前会话的session id(sid):

dbms_session.unique_session_id总共返回20位，其中前4位为session id，5~8位为serial#，具体示例请参考：统计oracle每个用户的事务数

view plaincopy to clipboardprint?

1. 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;

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>