In this post I will explain How to Delete&Purge Audit Trail Table.

Auditing is the monitoring and collection of some user database actions and record these data in some tables. Auditing is always enabled. Oracle Database generates audit records during or after the execution phase of the audited SQL statements. But AUD$ table will growth and will impact the performance of database.

The AUD$ table is used for the database auditing purpose (CREATE SESSION, LOGON, LOGOFF, SELECT, INSERT, DELETE) when AUDIT_TRAIL is set to either DB or DB_EXTENDED value. From 11g forwards AUDIT_TRAIL is activated by default and it is set to DB.

So we can Purge AUD$ Table Using DBMS_AUDIT_MGMT. So let us see how we can achieve this.

1. First, Check your current parameters

SQL> show parameter                                                                 

    NAME           TYPE    VALUE
--------------   -------   -----  
audit_trail       string    DB
 

2. Let us check AUD$ table is in SYSTEM table tablespace or not.

SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024/1024) as SizeinGB from dba_segments where segment_name=’AUD$’;

OWNER SEGMENT_NAME     SEGMENT_TYPE TABLESPACE_NAME SizeinGB
------ -------------- ------------ ---------------- ----------
SYS         AUD$        TABLE          SYSTEM          35

3. If AUD$ table is in SYSTEM tablespace , then move it to a new tablespace such as AUDIT_TS.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUDIT_TS');
END;
/

Check AUD$ table is moved to new tablespace or not

SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024/1024) as SizeinGB from dba_segments where segment_name=’AUD$’;

OWNER SEGMENT_NAME     SEGMENT_TYPE TABLESPACE_NAME SizeinGB
------ -------------- ------------ ---------------- ----------
SYS         AUD$        TABLE          AUDIT_TS        35

4. Now we can check which ways can be use for maintenance purge SYS.AUD$ table.

Option 1:
Regularly run truncate command for SYS.AUD$ table.

TRUNCATE TABLE SYS.AUD$;

Option 2:
Purge the audit trail records by using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure.

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Option 3:
Initialize the clean job. If you haven’t moved the AUD$ table out of SYSTEM tablespace, then below script will move the AUD$ to SYSAUX tablespace by default.

BEGIN
DBMS_AUDIT_MGMT.init_cleanup(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,default_cleanup_interval => 12 /* set_hours */);
END;
/

Check whether initialization is success or not:

SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

References:
How to Truncate, Delete or Purge Rows from SYS.AUD$ (Doc ID 73408.1)