How to remove old Oracle Database audit events

Email It to Me Print this Page
Question How to remove old audit events when auditing Oracle Database?
Answer 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';
Was this information helpful?