oracle獲取當前會話(程式)session id(sid)

lingxyd_0發表於2012-05-12
Oracle會話Session

 

轉帖於：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 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 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; 
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>

相關文章