How to purge the Oracle unified audit trail

The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in a uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view, and is available in both single-instance and Oracle Database Real Application Clusters environments.

The unified audit trail can be purged using the DBMS_AUDIT_MGMT package:

sqlplus / as sysdba

select  count(*) from unified_audit_trail;

COUNT(*)
----------
2619

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  FALSE);
END;
/

PL/SQL procedure successfully completed.

select  count(*) from unified_audit_trail;

COUNT(*)
----------
1

You can also use the last_arch_timestamp, if you don’t want to purge all the audit record and kept the most recent record:

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,last_archive_time => TO_TIMESTAMP('sysdate-10','DD-MON-RRHH24:MI:SS.FF'));
END;
/

Remember of execute the DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; before the call to dbms_audit_mgmt.clean_audit_trail, this will cause more consistent / expected results.

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  TRUE);
END;
/

Leave a Reply

Your email address will not be published.