How to remove old Oracle Database audit events

Oracle Database
7.0-8.5
9.0-9.7
9.8
https://kb.netwrix.com/588
Copy Article URL Copied

How to remove old audit events when auditing Oracle Database?


Netwrix recommends removing old audit events periodically to reduce load on the database server while auditing.
You can use the following Oracle Database packages:

The example below describes how to create the job to remove audit events by the following criteria:

  • Audit type—Unified Auditing
  • Event Age—Older than one day

The job is triggered once a day.

Review the example:

BEGIN
  DBMS_SCHEDULER.create_job(
job_name => ‘cleanup_unified_audit_job’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => TRUNC(SYSDATE)-1);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;’,
start_date => SYSTIMESTAMP,
auto_drop => FALSE,
enabled => TRUE,
repeat_interval => ‘FREQ=DAILY;INTERVAL=1’);
END;

Run the following queries to review results:

  • SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS where JOB_NAME = ‘CLEANUP_UNIFIED_AUDIT_JOB’;
  • SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = ‘CLEANUP_UNIFIED_AUDIT_JOB’;

 

Originally KB2106 

Go Up