Oracle: Purge UINIFIED AUDIT TRAIL

1. Get the count from audsys table:

    select count(1) from audsys.aud$unified;

2. Get the count from unified_audit_trail view:

    select  count(*) from unified_audit_trail;

3. Get the list of unified audit polices with action name and count

select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name;

4. Set the Timestamp that to be deleted as here it is set to 10 days before SYSDATE

begin

dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,last_archive_time=>sysdate-10);

end;

/

 5. Execute the below PL/SQL block to delete the UNIFIED_AUDIT_TRAIL to the timestamp set above

set serveroutput on;

begin

DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified

,use_last_arch_timestamp=>TRUE);

DBMS_OUTPUT.PUT_LINE('Time Ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

end;

/

6. Execute the below PL/SQL block to delete All UNIFIED_AUDIT_TRAIL

set serveroutput on;

begin

DBMS_OUTPUT.PUT_LINE('Time Started: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

use_last_arch_timestamp  =>  FALSE);

DBMS_OUTPUT.PUT_LINE('Time Ended: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

end;

/