oracle
数据库审计功能
audit_trail：参数设置启动或是关闭数据库的审计功能：
有none，
os
db
db，extended
xml
xml,extended
note：
当参数为db，extended或是xml，extended的时候会在aud$表中额外记录sql bind 和sql text clob-type的字段。
If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then
Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.
然后看一下数据库日志：
alter database open read only AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access SMON: enabling cache recovery Database Characterset is WE8MSWIN1252 Opening with Resource Manager plan: DEFAULT_MAINTENANCE_PLAN
可以看到当数据库audit_tail参数设置为db，但是我依然使用open read only打开的话，那么会自动转到os，当正常启动后，那么会自动转到db状态。
可以查看audit_file_dest参数，进行查看os文件所在位置：
eg： SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /opt/app/oracle/admin/RHYS/adu mp audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string OS SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 405020672 bytes Fixed Size 2213816 bytes Variable Size 251660360 bytes Database Buffers 146800640 bytes Redo Buffers 4345856 bytes Database mounted. Database opened. SQL>show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /opt/app/oracle/admin/RHYS/adu mp audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB SQL>
这里，我突然想起了一个老外（Nagaraj S)的问题，那就是如果审计内容非常大的话，那么system表空间会存在 空间不足情况，怎么自动删除。随后，很多国外的专家分别给出了自己的建议（如，Patterson,Joel,Steve Gardiner,Iggy Fernandez,Christopher等等）看如下邮件信息：
Hello Gurus,
I have a task to purge aud$ table and it need to done in automated way
on every month. Please help on sharing the purge script to schedule in db
scheduler
-Naga
好了，然后我们看看老外有什么好的办法。首先看看Ulfet的方法：
1）Archive and purge aud$ table
>创建新的表空间，创建新的归档表 ，创建过程，创建调度计划或是crontab，执行检查结果。
我实验结果如下：
eg：
[root@oracle-one ~]# su - oracle
[oracle@oracle-one ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 16 02:43:02 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 405020672 bytes
Fixed Size 2213816 bytes
Variable Size 251660360 bytes
Database Buffers 146800640 bytes
Redo Buffers 4345856 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual;
SYSDATE
---------
16-AUG-13
SQL> col name for a60
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------
1 /opt/app/oracle/RHYS/system01.dbf
2 /opt/app/oracle/RHYS/sysaux01.dbf
3 /opt/app/oracle/RHYS/undotbs01.dbf
4 /opt/app/oracle/RHYS/users01.dbf
5 /opt/app/oracle/RHYS/test.dbf
SQL> create tablespace arch_tbs datafile '/opt/app/oracle/RHYS/arch_tbs01.dbf' size 500M;
Tablespace created.
SQL> set pagesize 2000
SQL> select to_char(dbms_metadata.get_ddl('TABLE','AUD$')) FROM DU