Oracle Sequence Audses$研究

我们知道sqlplus连接Oracle时，会从sequence AUDSES$中取得Auditing session ID。研究版本为Oracle 10.2.0.4，需要注意的是在不同版本中略有不同。
[quote]SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production[/quote]
我们用普通业务用户连接至Oracle，注意并没有使用sysdba连接。取得当前会话的Auditing session ID
[quote]SQL> select USERENV('SESSIONID') from dual;

USERENV('SESSIONID')
--------------------
640503[/quote]
同样在V$SESSION也有所体现
[quote]SQL> select SID,AUDSID,USERNAME,TYPE,PROGRAM
2 FROM V$SESSION WHERE TYPE='USER';

SID AUDSID USERN TYPE PROGRAM
---------- -------------- ----- ---------- ------------------------------
141 4294967295 SYS USER sqlplus@mcsvr (TNS V1-V3)
142 [color=red]640503[/color] ZHOUL USER sqlplus@mcsvr (TNS V1-V3)[/quote]
可以查看AUDSES$下一个取值。
[quote]SQL> SELECT SYS.AUDSES$.NEXTVAL FROM DUAL;

NEXTVAL
----------
640504[/quote]
进一步查看audses$的定义
[quote]SQL> SELECT DBMS_METADATA.GET_DDL('SEQUENCE','AUDSES$') FROM DUAL;

DBMS_METADATA.GET_DDL('SEQUENCE','AUDSES$')
--------------------------------------------------------------------------------
CREATE SEQUENCE "SYS"."AUDSES$" MINVALUE 1 MAXVALUE 2000000000 INCREMENT BY 1 START WITH 77409 CACHE 10000 NOORDER CYCLE[/quote]

我们用sysdba连接至Oracle，取得当前会话的Auditing session ID，发现sysdba权限的Auditing session ID并没有从AUDSES$取得,因为AUDSES$最大值为2000000000，而当前SESSIONID已经达到4294967295（取自x$ksuse.KSUUDSES）
[quote]$ sqlplus "/as sysdba"
SQL> select USERENV('SESSIONID') from dual;

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

SQL> select sid from v$mystat where rownum=1;

SID
----------
141

SQL> SELECT AUDSID,USERNAME,TYPE,PROGRAM
2 FROM V$SESSION WHERE SID=141;

AUDSID USERN TYPE PROGRAM
-------------- ----- ---------- ------------------------------
4294967295 SYS USER sqlplus@mcsvr (TNS V1-V3)[/quote]


进一步查看后台核心进程的Auditing session ID，都取值为0
[quote]SQL> select SID,AUDSID,USERNAME,TYPE,PROGRAM
2 FROM V$SESSION WHERE TYPE='BACKGROUND';

SID AUDSID USERN TYPE PROGRAM
---------- -------------- ----- ---------- ------------------------------
150 0 BACKGROUND oracle@mcsvr (q001)
151 0 BACKGROUND oracle@mcsvr (q000)
154 0 BACKGROUND oracle@mcsvr (QMNC)
160 0 BACKGROUND oracle@mcsvr (SMON)
161 0 BACKGROUND oracle@mcsvr (MMNL)
162 0 BACKGROUND oracle@mcsvr (MMON)
163 0 BACKGROUND oracle@mcsvr (CJQ0)
164 0 BACKGROUND oracle@mcsvr (RECO)
165 0 BACKGROUND oracle@mcsvr (DBW0)
166 0 BACKGROUND oracle@mcsvr (CKPT)
167 0 BACKGROUND oracle@mcsvr (LGWR)

SID AUDSID USERN TYPE PROGRAM
---------- -------------- ----- ---------- ------------------------------
168 0 BACKGROUND oracle@mcsvr (MMAN)
169 0 BACKGROUND oracle@mcsvr (PSP0)
170 0 BACKGROUND oracle@mcsvr (PMON)

14 rows selected.[/quote]

在Oracle 10.2.0.4中可以看到sequence audses$的cache size已经变为10000。

[quote]SQL> select sequence_owner,sequence_name,cache_size
2 from dba_sequences
3 where sequence_name='AUDSES$';

SEQUENCE_OWNER SEQUENCE_NAME CACHE_SIZE
------------------------------ ------------------------------ ----------
SYS AUDSES$ [color=red]10000[/color][/quote]

而在Oracle 9.2.0.4中
[quote]SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production[/quote]
如果用sysdba权限连接用户和后台进程（BACKGROUND）一样，其AUDSID为0
[quote]SQL> SELECT SID,AUDSID,USERNAME,TYPE FROM V$SESSION;

SID AUDSID USERNAME TYPE
---------- ---------- ------------------------------ ----------
1 0 BACKGROUND
2 0 BACKGROUND
3 0 BACKGROUND
4 0 BACKGROUND
5 0 BACKGROUND
6 0 BACKGROUND
7 0 USER
8 0 BACKGROUND
9 0 BACKGROUND
10 0 SYS USER
12 0 BACKGROUND[/quote]
其cache_size为20.
[quote]SQL> select sequence_owner,sequence_name,cache_size
2 from dba_sequences
3 where sequence_name='AUDSES$';

SEQUENCE_OWNER SEQUENCE_NAME CACHE_SIZE
------------------------------ ------------------------------ ----------
SYS AUDSES$ 20[/quote]


另外值得注意的是：在10.2.3以下的Rac平台中，如果客户端频繁短连接，因cache_size只有20，可能会导致数据库挂起，可以详见metalink doc：395314.1