AUD$ Purge Script (Audit Table Maintenance) - Oracle

Here I share a direct-to-use AUD$ table maintenance script. For detailed information please visit my other post on table maintenance.

Pre-requisite:

Clean-up should be initialized using DBMS_AUDIT_MGMT package. Below is a sample code to initialize for 40 days (960 hours).

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
 DEFAULT_CLEANUP_INTERVAL => 24*40 /* hours x days */
);
END;
/

In case you see this error:
ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation

Change the default tablespace to 'USERS' or any other tablespace which has sufficient space.
UPDATE dam_config_param$ SET string_value='USERS' WHERE audit_trail_type#=1 AND param_id=22;
Commit;

Change it back after INIT_CLEANUP is successful
UPDATE dam_config_param$ SET string_value='SYSAUX' WHERE audit_trail_type#=1 AND param_id=22;
Commit;

Script:

Below script would
# Create a job using DBMS_AUDIT_MGMT.CREATE_PURGE_JOB to purge using LAST_ARCH_TS

# Modify job to start from 15th April 2019 at 01:00 AM and run every 24 hours
# Modify job to Set LAST_ARCH_TS (last archive timestamp) to purge audit data older than 40 days
# Add notification to alertlog when job starts and completes

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  AUDIT_TRAIL_PURGE_INTERVAL => 24,
  AUDIT_TRAIL_PURGE_NAME => 'JOB_PURGE_AUDIT_TRAIL',
  USE_LAST_ARCH_TIMESTAMP => TRUE
);
DBMS_SCHEDULER.SET_ATTRIBUTE (
  name      => 'JOB_PURGE_AUDIT_TRAIL',
  attribute => 'START_DATE',
  value => TO_TIMESTAMP('15-APR-2019 01:00','DD-MON-YYYY HH24.MI'));
DBMS_SCHEDULER.SET_ATTRIBUTE (
  name      => 'JOB_PURGE_AUDIT_TRAIL',
  attribute => 'JOB_ACTION',
  value => 'BEGIN
   /* AUD$ Purge Script - Anurag T. - 14/Apr/2019 */
   SYS.DBMS_SYSTEM.KSDWRT(2, ''Starting Audit Data Purge - At'');
   SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
   (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME => SYSTIMESTAMP - 40
   );
   SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
   (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   LAST_ARCHIVE_TIME => SYSTIMESTAMP - 40
   );
   SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
   (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   LAST_ARCHIVE_TIME => SYSTIMESTAMP - 40
   );
   SYS.DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
   (
   AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   LAST_ARCHIVE_TIME => SYSTIMESTAMP - 40
   );
   SYS.DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);
   SYS.DBMS_SYSTEM.KSDWRT(2, ''Completed Audit Data Purge - At'');
END;');
DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
  audit_trail_purge_name      => 'JOB_PURGE_AUDIT_TRAIL',
  audit_trail_status_value    => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

Verify:

SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

Defragment Audit Tables (Optional)

In case size of your AUD$ of FGA_LOG$ table is still large after purge, it can be defragmented in 11g onwards using below commands or using alter table enable row movement and alter table shrink in older releases

First 4 commands move all tables to another tablespace and subsequent 4 commands will move them back to original tablespace.

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   audit_trail_location_value  => 'USERS');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   audit_trail_location_value  => 'USERS');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   audit_trail_location_value  => 'USERS');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   audit_trail_location_value  => 'USERS');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   audit_trail_location_value  => 'SYSAUX');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
   audit_trail_location_value  => 'SYSAUX');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   audit_trail_location_value  => 'SYSAUX');
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   audit_trail_location_value  => 'SYSAUX');
END;
/

Done! Cheers.
Anurag