How To Purge The UNIFIED AUDIT TRAIL ?

by

Oracle Database – Enterprise Edition – Version 12.1.0.1 and later

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
sqlplus / as sysdba
 
SQL>  select  count(*) from unified_audit_trail;
 
  COUNT(*)
----------
      3621
 
SQL>
 
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.
 
SQL>  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:

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,last_archive_time => TO_TIMESTAMP('10-SEP-23 14:10:10.0','DD-MON-RRHH24:MI:SS.FF'));
END;
/
 
 
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   use_last_arch_timestamp  =>  TRUE);
END;
/

Since Unified Auditing caches the audit trail in memory to implement a ‘lazy write’ feature that helps performance, some of the records eligible for deletion may still linger in the cache, to also first flush this cache add a call to: DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; before the call to
dbms_audit_mgmt.clean_audit_trail, this will cause more consistent / expected results.


If a purge job needs to be configured for unified audit trail and automatic advancement of the last_archive_timestamp following code can be used

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'CLEANUP_AUDIT_TRAIL_UNIFIED',
use_last_arch_timestamp => TRUE);
END;
/
 
 
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC(SYSTIMESTAMP)-10);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Automatically set audit last archive time.');
END;

The following code can be used to combine purge job & automatic advancement of archive timestamp in one scheduler code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'PURGE_UNIFIED_AUDIT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, SYSTIMESTAMP-31);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  TRUE);
END;',
start_date => '',
repeat_interval => 'freq=daily; byhour=3; byminute=10; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Purge unified audit trail older than 31 days.');
END;
/

Hope it worked !! 🙂