Oracle Connector

-- Check current audit trail size
SELECT
  TABLESPACE_NAME,
  ROUND(SUM(BYTES)/1024/1024, 2) AS SIZE_MB
FROM DBA_SEGMENTS
WHERE OWNER = 'AUDSYS'
GROUP BY TABLESPACE_NAME;

-- View partition information
SELECT
  PARTITION_NAME,
  HIGH_VALUE,
  NUM_ROWS,
  ROUND(BYTES/1024/1024, 2) AS SIZE_MB
FROM DBA_TAB_PARTITIONS p
JOIN DBA_SEGMENTS s ON p.PARTITION_NAME = s.PARTITION_NAME
WHERE p.TABLE_OWNER = 'AUDSYS'
  AND p.TABLE_NAME = 'AUD$UNIFIED'
ORDER BY PARTITION_POSITION;

-- Check last archive timestamps
SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

-- Move audit data to dedicated tablespace (recommended)
-- First create a dedicated tablespace
CREATE TABLESPACE AUDIT_DATA
  DATAFILE '/u01/oradata/ORCL/audit_data01.dbf' SIZE 10G
  AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- Move the unified audit trail
BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value => 'AUDIT_DATA'
  );
END;
/

-- Change partition interval to daily (recommended for high-volume)
BEGIN
  DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
    interval_number    => 1,
    interval_frequency => 'DAY'
  );
END;
/

-- Or weekly for moderate volume
BEGIN
  DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
    interval_number    => 7,
    interval_frequency => 'DAY'
  );
END;
/

-- Default is monthly
BEGIN
  DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
    interval_number    => 1,
    interval_frequency => 'MONTH'
  );
END;
/

-- Set batch size for efficient deletion
BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_property => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
    audit_trail_property_value => 100000
  );
END;
/

-- Set last archive timestamp (retain last 30 days)
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - INTERVAL '30' DAY
  );
END;
/

-- Execute the purge
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    use_last_arch_timestamp => TRUE
  );
END;
/

-- Create automated daily purge job (recommended)
-- This job runs every 24 hours to clean audit records older than 30 days

BEGIN
  -- Create the purge job
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_purge_interval => 24,  -- Run every 24 hours
    audit_trail_purge_name => 'DAILY_UNIFIED_AUDIT_PURGE',
    use_last_arch_timestamp => TRUE
  );
END;
/

-- Create a scheduler job to update the archive timestamp before each purge
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'UPDATE_AUDIT_ARCHIVE_TS',
    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 => SYSTIMESTAMP - INTERVAL ''30'' DAY
        );
      END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',  -- Run at 2 AM
    enabled => TRUE,
    comments => 'Update archive timestamp for audit purge - retain 30 days'
  );
END;
/

-- Verify jobs are created
SELECT JOB_NAME, STATE, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME LIKE '%AUDIT%';

-- Check purge job status
SELECT
  JOB_NAME,
  JOB_ACTION,
  STATE,
  LAST_START_DATE,
  NEXT_RUN_DATE,
  RUN_COUNT,
  FAILURE_COUNT
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME LIKE '%AUDIT%';

-- View purge job history
SELECT
  JOB_NAME,
  LOG_DATE,
  STATUS,
  ERROR#,
  ADDITIONAL_INFO
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME LIKE '%AUDIT%'
ORDER BY LOG_DATE DESC
FETCH FIRST 10 ROWS ONLY;

-- Check current archive timestamps
SELECT
  AUDIT_TRAIL,
  RAC_INSTANCE,
  LAST_ARCHIVE_TS
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

-- Monitor tablespace usage after purge
SELECT
  TABLESPACE_NAME,
  ROUND(USED_SPACE * 8192 / 1024 / 1024, 2) AS USED_MB,
  ROUND(TABLESPACE_SIZE * 8192 / 1024 / 1024, 2) AS TOTAL_MB,
  ROUND(USED_PERCENT, 2) AS USED_PCT
FROM DBA_TABLESPACE_USAGE_METRICS
WHERE TABLESPACE_NAME = 'AUDIT_DATA';

-- Disable the purge job temporarily
BEGIN
  DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
    audit_trail_purge_name => 'DAILY_UNIFIED_AUDIT_PURGE',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE
  );
END;
/

-- Re-enable the purge job
BEGIN
  DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS(
    audit_trail_purge_name => 'DAILY_UNIFIED_AUDIT_PURGE',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE
  );
END;
/

-- Drop a purge job completely
BEGIN
  DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
    audit_trail_purge_name => 'DAILY_UNIFIED_AUDIT_PURGE'
  );
END;
/