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

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

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

view plain

copy to clipboard

print

?

selectsid

fromv$mystat

whererownum = 1;

select sid

from v$mystat

where rownum = 1;

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

view plain

copy to clipboard

print

?

selectsid

fromv$session

whereaudsid=userenv('sessionid');

select sid

from v$session

where 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 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> show user

USER 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 SYS

9 rows selected.

SQL> select userenv('sessionid') from dual;

USERENV('SESSIONID')

--------------------

0

3.oracle 10g 用userenv(‘sid’)获取session id(sid):

view plain

copy to clipboard

print

?

--oracle 10g以后可以使用

selectuserenv('sid')fromdual;

--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 plain

copy to clipboard

print

?

select

to_number(substrb(dbms_session.unique_session_id,1,4),'xxxx') sid,

to_number(substrb(dbms_session.unique_session_id,5,4),'xxxx') audsid

fromdual;

select

to_number(substrb(dbms_session.unique_session_id,1,4),'xxxx') sid,

to_number(substrb(dbms_session.unique_session_id,5,4),'xxxx') audsid

from 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 7651

SQL>