Oracle数据库 SYS.AUDSES$ 序列 说明

SYS.AUDSES$ 序列 说明

MOS 说明

MOS 文档：How Sessions get Their AUDSID Identifier [ID 122230.1]

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

当session 连接到Oracle 数据库时，会分配一个标识号，这个标识号可以从v$session 这个视图里查询。

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

其中一个标识符就是v$session.audsid. 这个标识符非常有用，因为它也可以用SQL 函数USERENV(‘SESSIONID’)来获取，这样，session 就可以发现其关联的信息。

SESSION 获取v$session.audsid说明

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:

-- 查看当前的Auditingsession id
SQL> select userenv('SESSIONID') from dual;

USERENV('SESSIONID')

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

               13591

This value maybe used to find out more information about the current session as it is also recorded in the column AUDSID of the V$SESSIONdynamicview.通过这个audid，然后和v$session 结合，就可以查看session的更多信息。

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 aconstant value for the duration of the session.

AUDID 决定用户是什么时候连接到数据库的，在session 活动期间，该值是不变的。

The value thatis assigned to AUDSID is taken from a sequence in the Oracle Data Dictionary,the sequence SYS.AUDSES$.WhenAUDSIDisassignedtoanewsessiontheSYS.AUDSES$ sequence is incremented using NEXTVALand the new value is assigned to the session.
那么session 分配的AUDSID 值是从SYS.AUDSES$序列中获取的，当分配时，SYS.AUDSES$会使用nextval 增加，然后赋值给session。

However, not allsessions get an AUDSID: Sessions connecting as ‘internal’ do not increment theSYS.AUDSES$ sequence and the value of their AUDSID is 0.
但是，不是所有的session 都可以获取到AUDSID值。 用internal连接时，就不会增加SYS.AUDSES$序列，其对应session 的AUDSID 值是0.

For example, see what happens when weconnect internal in svrmgrl:

SVRMGR> connect internal

Connected.


SVRMGR> select userenv('SESSIONID') fromdual;

USERENV('S

----------

        0

1 row selected.

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

不管使用什么工具连接，只要用的是internal用户，那么AUDSID 就是0

For example, connecting internal from SQL*Plus:

SQL> connect internal/oracle

Connected.

SQL> select userenv('SESSIONID') fromdual;

 

USERENV('SESSIONID')

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

                   0

Background processes also have 0 as their AUDSID value。

后台进程对应的AUDSID值也是0.

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

SQL> select sid,serial#,audsid,programfrom 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’ andin both of these cases AUDSID is again 0.

internal 进行连接，和’as sysoper’,’as sysdba’ 连接是等价的。其对应的AUDSID 都是0.

In 10g we make a further distinction forSYS sessions:

If AUDSID=0, then it is an internallygenerated SYS session.

If AUDSID=UB4MAX(4294967295), then it is adirect SYS session.
在Oracle 10g，如果AUDSID 为0. 则表示的使用内部生成的SYS session，如果AUDSID 为UB4MAX(4294967295), 那么表示直接使用SYS用户登陆的。

SYS@anqing1(rac1)> conn / as sysdba;
Connected.

SYS@anqing1(rac1)> selectuserenv('SESSIONID') from dual;

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

小结

• 当session连接数据库时，会分配一个Auditing Session ID，可以使用userenv(‘SESSIONID’) 查看当前AUDID 的值，其值和v$session 视图里的audid 等值，所以结合这个，就可以查看当前session 的更多信息。
• Auditing session ID的值是从SYS.AUDSES$序列中获取，每次取时会自动增加，然后赋给session。
• 对于internal用户（’/as sysoper’ 和 ‘/as sysdba’）和后台进程，其对应的AUDID 为0.
• 在Oracle 10g中，如果AUDID的值为0，表明是internal 用户，如果AUDID 值是4294967295，那么就表明是用SYS 用户直接连接的。

SYS.AUDSES$的优化

参考资料

https://blog.itpub.net/500314/viewspace-1589523