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(*)
----------
1You 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;
/