Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.  The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier.

Follow below steps for puring aud$ table.

1. Make sure AUD$ table is not in SYSTEM table tablespace.

  If AUD$ table is present in SYSTEM tablespace , then move it to a a new dedicated tablespace using below script.

 

 

2. Check the audit mgmt configuration parameters:

 

3. Initialize the clean job:

Note – If you haven’t moved the AUD$ table out of SYSTEM tablespace, then it the below script will move the AUD$ to SYSAUX tablespace by default.

4. Validate whether initialization is success or not:

It should return YES

 

5. Set LAST_ARCHIVE_TIMESTAMP:

             This value allows the audit record to be purged before a particular date. Suppose LAST_ARCHIVE_TIME is set to SYSTIMESTAMP -30, then it will keep only recent 30 days record and purge the all audit records dated before 30 days.

6. If you want to run the purge instantly( one time):

 7. Schedule a job to purge AUD$ regularly.

Below job will run daily and delete all the aud$ records before systimestamp- 30 .

8. Updating audit properties.

Note:-   If the AUD$ table, lot of rows ( around 2 to 3 crores), then setting the DB AUDIT CLEAN BATCH SIZE to a bit higher values (around 2 to 3 lakh) will increase the performance.  Keeping a low value will increase the time taken for purge and a very high value will led to higher undo generation.