RelationalDBDesign RelationalDBDesign



Auditing Features  «Prev  Next»
Lesson 8 Purging the audit trail
Objective Delete old audit trail records.

Purging the Audit Trail in Oracle

As you can imagine, auditing can take up a lot of space in your database. There are times when you will use auditing to create a record of database activity, but you will also frequently use auditing simply to diagnose a problem. Once the problem is identified, you will no longer need the audit files.
In addition, audit records are stored in the SYS.AUD$ table, which, by default, contains 99 extents of 10K each. If the space allowed for this table fills up, you can no long perform any auditing.

Purging audit trails

To clean out old audit records, simply delete rows from the SYS.AUD$ table, just as you would with any other table.
The only people with permission to do this are the user SYS, anyone whom SYS has granted DELETE privilege on the AUD$ table to, or anyone with DELETE ANY TABLE privilege.
If you want to delete a certain set of rows in the table, use a WHERE clause to limit the action. For instance, if you want to delete all the audit rows in a table that related to the COIN table, use the following SQL statement:

DELETE * FROM SYS.AUD$ WHERE OBJ$NAME = 'COIN';

If you want to save audit records before deleting them, use the standard SQL command to insert the rows from the SYS.AUD$ table into another table. For instance, if you want to save the audit rows for the COIN table into the table named COIN_AUDIT, simply use the statement:

INSERT INTO coin_audit SELECT * FROM SYS.AUD$
WHERE obj$name = 'COIN';

assuming that the column structure of the COIN_AUDIT table matches that of the SYS.AUD$ table.
The next lesson is the module conclusion.