Auditing refers to the process of monitoring and collecting specific user database actions and storing this information in designated tables. It is a feature that is consistently enabled in Oracle Database. During or after the execution of audited SQL statements, Oracle Database generates audit records. However, it’s important to note that the growth of the AUD$ table can have an impact on the overall performance of the database.

Starting from Oracle Database 11g and onwards, when the AUDIT_TRAIL parameter is set to either the “DB” or “DB_EXTENDED” value, the auditing feature is activated by default. This means that the database will automatically generate audit records for audited SQL statements and store them in the AUD$ table. The default value for AUDIT_TRAIL is “DB”, indicating that the database auditing is enabled.

By setting AUDIT_TRAIL to “DB”, the database ensures that basic auditing is performed, capturing essential information about user actions and database activities. The “DB_EXTENDED” value provides more detailed auditing, capturing additional information such as bind variables and other specific details related to the SQL statements.

It’s important to note that while the AUDIT_TRAIL parameter is set to “DB” by default, it can be modified to other values based on specific auditing requirements. This allows administrators to customize the auditing configuration according to their needs, whether it involves more detailed auditing, sending audit records to the operating system audit trail, or redirecting them to a different destination.

Overall, starting from Oracle Database 11g, AUDIT_TRAIL is activated by default, set to “DB”, ensuring that the database generates audit records for audited SQL statements, unless it is explicitly disabled or modified to a different value.

To maintain the AUD$ table and manage its growth effectively, you can follow these step-by-step actions:

1. First, Check your current parameters

SQL> show parameter                                                                 

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

     2. Let us check which tablespace is using by AUD$ table. Default tablespace is in SYSTEM tablespace.

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          115

    3. In In this case, our Auditing table AUD$ is in SYSTEM tablespace so we will create a new tablespace named “AUDIT_TS”  and then move the AUD$ to our newly created Tablespace

    4. After creating a new tablespace run the below command as sysdba user.

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;
/

    5. Now check AUD$ table is moved to our new tablespace with the query given below:

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       115

     6. Now we can use some options to maintain for SYS.AUD$ table.,

Here are Some options that can be used to Maintain and Purge the Audit Records:

1: Regularly run the truncate command for SYS.AUD$ table by Syntax given below:

TRUNCATE TABLE SYS.AUD$;

 2: Purge the audit trail records by using DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure by Syntax given below:

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

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

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 a success or not by the Procedure Given Below:

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;
/