如何理解v$session中的audsid字段呢？

etl2007

找了一些资料例如
AUDSID：number：Auditing session ID
AUDSID ：审查session ID唯一性，确认它通常也用于当寻找并行查询模式   链接
The column audsid can be joined with sys_context('userenv','SESSIONID') to find out which session is the "own one". Alternatively, dbms_support.mysid can be used.

但仍然感觉理解很晦涩？请问大刘能否给出更细致的解释呢

[ 本帖最后由 etl2007 于 2012-5-11 07:49 编辑 ]

Maclean Liu(刘相兵

ODM FINDING:

How Sessions get Their AUDSID Identifier

PURPOSE
-------

Sessions connecting to an Oracle database have a number of identifiers
assigned to them which can be found in the V$SESSION dynamic view.

One of these identifiers is V$SESSION.AUDSID.  This identifier is particularly
useful because it can also be obtained by a session using the SQL function
USERENV('SESSIONID').  In this way, a session can find its own session-related
information.

This Bulletin explains how the value of the identifier V$SESSION.AUDSID is
determined for a session.


SCOPE & APPLICATION
-------------------

This Bulletin may be useful to Database Administrators and Application
Developers.


HOW A SESSION GETS ITS V$SESSION.AUDSID VALUE
---------------------------------------------

A session connected to an Oracle database may obtain one of its session
identifiers, the Auditing Session ID, by use of the built-in USERENV SQL
function.

For example, in SQL*Plus, a query like the following can be used:

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

USERENV('SESSIONID')
--------------------
               13591

This value may be used to find out more information about the current session
as it is also recorded in the column AUDSID of the V$SESSION dynamic view.

Continuing the previous example:

SQL> select sid,serial#,audsid,program from v$session where audsid=13591;

       SID    SERIAL#     AUDSID  PROGRAM
---------- ---------- ----------  ------------------------------------------
        13       2904      13591  sqlplus@sunclient1 (TNS V1-V2)

This Auditing Session ID is determined when the user connects to the database
and is a constant value for the duration of the session.

The value that is assigned to AUDSID is taken from a sequence in the Oracle
Data Dictionary, the sequence SYS.AUDSES$.  When AUDSID is assigned to a new
session the SYS.AUDSES$ sequence is incremented using NEXTVAL and the new
value is assigned to the session.

However, not all sessions get an AUDSID: Sessions connecting as 'internal'
do not increment the SYS.AUDSES$ sequence and the value of their AUDSID is 0.

For example, see what happens when we connect internal in svrmgrl:

SVRMGR> connect internal
Connected.

SVRMGR> select userenv('SESSIONID') from dual;
USERENV('S
----------
         0
1 row selected.

Connect internal gets an AUDSID of 0 irrespective of which tool was used.

For example, connecting internal from SQL*Plus:

SQL> connect internal/oracle
Connected.
SQL> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')
--------------------
                   0

Background processes also have 0 as their AUDSID value:

Continuing from the last SQL*Plus session, where we are connected internal:

SQL> select sid,serial#,audsid,program from v$session;

       SID    SERIAL#     AUDSID PROGRAM
---------- ---------- ---------- ------------------------------
         1          1          0 oracle@sunclient1 (PMON)
         2          1          0 oracle@sunclient1 (DBWR)
         3          1          0 oracle@sunclient1 (ARCH)
         4          1          0 oracle@sunclient1 (LGWR)
         5          1          0 oracle@sunclient1 (CKPT)
         6          1          0 oracle@sunclient1 (SMON)
         7          1          0 oracle@sunclient1 (RECO)
         8      17125          0 sqlplus@sunclient1 (TNS V1-V2)

10 rows selected.

Of course 'connect internal' is equivalent to connecting 'AS SYSOPER'
or 'AS SYSDBA' and in both of these cases AUDSID is again 0.


In 10g we make a further distinction for SYS sessions:  
If AUDSID=0, then it is an internally generated SYS session.  
If AUDSID=UB4MAX(4294967295), then it is a direct SYS session.

Maclean Liu(刘相兵

This Auditing Session ID is determined when the user connects to the database
and is a constant value for the duration of the session.

The value that is assigned to AUDSID is taken from a sequence in the Oracle
Data Dictionary, the sequence SYS.AUDSES$.  When AUDSID is assigned to a new
session the SYS.AUDSES$ sequence is incremented using NEXTVAL and the new
value is assigned to the session.

However, not all sessions get an AUDSID: Sessions connecting as 'internal'
do not increment the SYS.AUDSES$ sequence and the value of their AUDSID is 0.


AUDSID 来源于 SYS.AUDSES$ 的序列， 用于 审计。

session A:

SQL> conn maclean/maclean
Connected.
SQL> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')
--------------------
              160116

session B;
SQL> conn maclean/maclean
Connected.
SQL> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')
--------------------
              160117


session C:

若使用sys 用户登录 则SESSIONID 为0 或者 4294967295

SQL> conn / as sysdba
Connected.
SQL> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')
--------------------
          4294967295