关于oracle11g的审计功能

现象：
今天上午发现外网数据库有几个sql特别的慢，经过分析这几个sql是往aud$基表中插数据的sql，这个表现在有记录数5千多万，占据了系统表空间约10g的空间，这样会导致数据库的性能下降（虽然oracle11g文档说性能影响可以忽略，但是通过我们的观察，确实带来性能下降明细），这样下去，随着审计记录的增长，会有将系统表空间撑爆的可能性。
另外查看了其他几个11g的数据库，都有这个现象，只是其他库的这张基表的数据目前还没有那么大（几十万行记录）。

原因分析：
在oracle11g中，数据库的审计功能是默认开启的（这和oracle10g的不一样，10g默认是关闭的），oracle11gR2的官方文档上写的是错的，当上说default是none，而且是审计到DB级别的，这样就会往aud$表里记录统计信息。

问题处理方法：
1.如果审计不是必须的，可以关掉审计功能；
SQL> alter system set audit_trail=none scope=spfile;
SQL>showdown immediate;
SQL>startup
2.删除已有的审计信息
可以直接truncate表aud$,
或者采取dbms_audit_mgmt来清除。
3.或者将aud$表移到另外一个表空间下，以减少system表空间的压力和被撑爆的风险。

附：11g中有关audit_trail参数的设置说明：

AUDIT_TRAIL

Property                     Description

Parameter                 type String
Syntax                        AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }
Default value            none
Modifiable                 No
Basic                         No



AUDIT_TRAILenables or disables database auditing.

Values:


none

Disables standard auditing. This value is the default if theAUDIT_TRAILparameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default isdb.


os

Directs all audit records to an operating system file. Oracle recommends that you use theossetting, particularly if you are using an ultra-secure database configuration.


db

Directs audit records to the database audit trail (theSYS.AUD$table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.

If the database was started in read-only mode withAUDIT_TRAILset todb, then Oracle Database internally setsAUDIT_TRAILtoos. Check the alert log for details.


db, extended

Performs all actions ofAUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of theSYS.AUD$table, when available. These two columns are populated only when this parameter is specified.

If the database was started in read-only mode withAUDIT_TRAILset todb, extended, then Oracle Database internally setsAUDIT_TRAILtoos. Check the alert log for details.


xml

Writes to the operating system audit record file in XML format. Records all elements of theAuditRecordnode exceptSql_TextandSql_Bindto the operating system XML audit file.


xml, extended

Performs all actions ofAUDIT_TRAIL=xml, and populates the SQL bind and SQL text CLOB-type columns of theSYS.AUD$table, wherever possible. These columns are populated only when this parameter is specified.

You can use the SQLAUDITstatement to set auditing options regardless of the setting of this parameter.