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