转帖于: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
?
selectsid
fromv$mystat
whererownum = 1;
select sid
from v$mystat
where rownum = 1;
2.用userenv函数获取oracle当前会话的session id(sid):
view plain
copy to clipboard
?
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
?
--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
?
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>