Skip to content

How to Purge Unified Audit Trail in Oracle

  • Oracle

Tablespace SYSAUX

Since tablespace SYSAUX has grown too big, so we query V$SYSAUX_OCCUPANTS to check the usage of AUDSYS in SYSAUX like this:

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

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

It seems that the repository of unified audit trail owned by AUDSYS is taking too much space in tablespace SYSAUX.

In fact, the repository table is AUD$UNIFIED, a partitioned table which stores all audit trails. We usually query it through public synonym UNIFIED_AUDIT_TRAIL.

To purge AUD$UNIFIED, we leverage DBMS_AUDIT_MGMT package and take the following steps to reach our goal.

SET_LAST_ARCHIVE_TIMESTAMP

To keep some latest events, we can set LAST_ARCHIVE_TIMESTAMP to limit the scope of purging.

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

In this case, we keep records within 30 days from now.

To add or substract some days from SYSTIMESTAMP, we use a different way from SYSDATE.

Optionally, you can make sure that LAST_ARCHIVE_TIMESTAMP has been set correctly.

CLEAN_AUDIT_TRAIL

We actually purge unnecessary audit trails according to LAST_ARCHIVE_TIMESTAMP.

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

Remove All Events

To purge all audit trails definitely, we can flag USE_LAST_ARCH_TIMESTAMP as FALSE.

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

If it seems to be running slowly, you may consider to speed up the deletion of unified audit trails.

CLEAR_LAST_ARCHIVE_TIMESTAMP

We should reset LAST_ARCHIVE_TIMESTAMP in case of being misunderstood in the future.

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

Check SYSAUX

Let's check the space again.

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

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

The space is freed.

Leave a Reply

Your email address will not be published. Required fields are marked *