Purge Unified Audit Trail in Oracle to Free Up SYSAUX Space

Oracle’s unified audit trail can silently consume significant space in the SYSAUX tablespace, especially when audit logs aren’t purged regularly. If you’re noticing SYSAUX growing unusually large, it may be time to clean up the audit data stored by the AUDSYS schema.

Here’s a practical guide to identify the issue and purge old unified audit records using Oracle’s DBMS_AUDIT_MGMT package.

Step 1: Check SYSAUX Usage by AUDSYS

Connect as SYSDBA and inspect how much space AUDSYS is using:

SQL> show user
USER is "SYS"

SQL> column occupant_name format a30;
SQL> column occupant_desc format a30;
SQL> column size_gb format 999.99;

SQL> select occupant_name, occupant_desc, space_usage_kbytes/1024/1024 size_gb
     from v$sysaux_occupants
     where occupant_name = 'AUDSYS';

Example output:

OCCUPANT_NAME     OCCUPANT_DESC           SIZE_GB
----------------- ------------------------ -------
AUDSYS            AUDSYS schema objects     58.05

This indicates that audit data is heavily occupying SYSAUX, primarily in the AUD$UNIFIED table.

Step 2: Retain Only Recent Audit Logs

Before purging, you may want to keep recent logs (e.g., from the last 30 days). Use SET_LAST_ARCHIVE_TIMESTAMP to define the cutoff:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    AUDIT_TRAIL_TYPE   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    LAST_ARCHIVE_TIME  => SYSTIMESTAMP - INTERVAL '30' DAY
  );
END;
/

This ensures only data older than 30 days is removed.

Step 3: Purge Older Audit Records

With the timestamp set, run the following to clean up old entries:

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

This deletes only those records older than the defined timestamp.

Optional: Remove All Audit Logs

To completely purge the audit trail (ignoring any retention settings), set the flag to FALSE:

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    AUDIT_TRAIL_TYPE        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    USE_LAST_ARCH_TIMESTAMP => FALSE
  );
END;
/

Be cautious: this will delete all unified audit logs.

Step 4: Clear the Archive Timestamp

Once done, it’s good practice to reset the archive timestamp:

BEGIN
  DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
  );
END;
/

This avoids confusion during future purging operations.

Step 5: Recheck SYSAUX Usage

Finally, verify the space savings:

SQL> select occupant_name, occupant_desc, space_usage_kbytes/1024/1024 size_gb
     from v$sysaux_occupants
     where occupant_name = 'AUDSYS';

Expected result:

OCCUPANT_NAME     OCCUPANT_DESC           SIZE_GB
----------------- ------------------------ -------
AUDSYS            AUDSYS schema objects     4.85

This confirms a successful cleanup.

Summary

Regular maintenance of the unified audit trail can significantly reduce storage consumption in Oracle’s SYSAUX tablespace. Using the DBMS_AUDIT_MGMT package provides a clean and efficient way to manage audit data while preserving recent logs for compliance or analysis.

Tip: Automate this process via a scheduled job to avoid future SYSAUX bloating.