Oracle Database Administration: The Essential Refe

Maintaining the Audit Trail

The audit trail table needs to be cleaned out on a periodic basis. You should periodically move the audit trail information out of the audit trail and into your own set of tables. There are several reasons why you need to maintain the audit trail in this way:

1. The audit trail base table, SYS.AUD$, is created in the SYSTEM tablespace as part of the database creation process. If you have any level of auditing turned on, the audit trail will usually grow to become the single largest table in the SYSTEM tablespace. You cannot afford to allow this single table and its index to use up all free space in the SYSTEM tablespace.

2. The audit trail is a view with a join; thus, if the table becomes large, queries to it will be slow. If you copy the information to a separate table and index it, your query performance may improve.

3. If you have auditing enabled BY ACCESS, or if you have many small transactions connecting to the database, you may have more information in the audit trail than you expect.

4. Because the audit trail base table, SYS.AUD$, is owned by SYS, it is not exported when a full or incremental export operation is performed. In the event of a database disaster that forces you to rebuild the database from an export, all existing audit trail information will be lost.

We recommend that you maintain the audit trail by following these steps:

1. Determine what information you need to keep over time. In addition, ask whether you need to keep this information as it is recorded ...