unified auditing

SET SERVEROUTPUT ON;

DECLARE

  v_min_timestamp TIMESTAMP;

  v_max_timestamp TIMESTAMP;

  v_record_count NUMBER;

  v_total_bytes NUMBER;

  v_used_bytes NUMBER;

  v_free_bytes NUMBER;

  v_percent_used NUMBER;

  v_start_time TIMESTAMP := SYSTIMESTAMP;

BEGIN

  -- Check record count and timestamps before purge

  SELECT COUNT(*), MIN(event_timestamp), MAX(event_timestamp)

  INTO v_record_count, v_min_timestamp, v_max_timestamp

  FROM unified_audit_trail;

  

  DBMS_OUTPUT.PUT_LINE('Before Purge (as of ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS') || '):');

  DBMS_OUTPUT.PUT_LINE('Record Count: ' || v_record_count);

  DBMS_OUTPUT.PUT_LINE('Min Timestamp: ' || NVL(TO_CHAR(v_min_timestamp, 'YYYY-MM-DD HH24:MI:SS'), 'NULL'));

  DBMS_OUTPUT.PUT_LINE('Max Timestamp: ' || NVL(TO_CHAR(v_max_timestamp, 'YYYY-MM-DD HH24:MI:SS'), 'NULL'));

  

  -- Check SYSAUX tablespace usage

  SELECT SUM(bytes) INTO v_total_bytes

  FROM dba_data_files

  WHERE tablespace_name = 'SYSAUX';

  

  SELECT SUM(bytes) INTO v_free_bytes

  FROM dba_free_space

  WHERE tablespace_name = 'SYSAUX';

  

  v_used_bytes := v_total_bytes - NVL(v_free_bytes, 0);

  v_percent_used := ROUND((v_used_bytes / v_total_bytes) * 100, 2);

  

  DBMS_OUTPUT.PUT_LINE('SYSAUX Tablespace:');

  DBMS_OUTPUT.PUT_LINE('Total Size: ' || ROUND(v_total_bytes / 1024 / 1024 / 1024, 2) || ' GB');

  DBMS_OUTPUT.PUT_LINE('Used Space: ' || ROUND(v_used_bytes / 1024 / 1024 / 1024, 2) || ' GB');

  DBMS_OUTPUT.PUT_LINE('Free Space: ' || ROUND(v_free_bytes / 1024 / 1024 / 1024, 2) || ' GB');

  DBMS_OUTPUT.PUT_LINE('Percent Used: ' || v_percent_used || '%');

  

  -- Set batch size for large dataset (19.8M records)

  BEGIN

    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

      audit_trail_type => 16, -- Unified Auditing

      audit_trail_property => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,

      audit_trail_property_value => 100000

    );

    DBMS_OUTPUT.PUT_LINE('Batch size set to 100,000.');

  EXCEPTION

    WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('Error setting batch size: ' || SQLERRM);

  END;

  

  -- Purge all Unified Auditing records

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

    audit_trail_type => 16, -- Unified Auditing

    use_last_arch_timestamp => FALSE

  );

  DBMS_OUTPUT.PUT_LINE('All Unified Auditing records purged.');

  

  -- Verify purge

  SELECT COUNT(*), MIN(event_timestamp), MAX(event_timestamp)

  INTO v_record_count, v_min_timestamp, v_max_timestamp

  FROM unified_audit_trail;

  

  DBMS_OUTPUT.PUT_LINE('After Purge:');

  DBMS_OUTPUT.PUT_LINE('Record Count: ' || v_record_count);

  DBMS_OUTPUT.PUT_LINE('Min Timestamp: ' || NVL(TO_CHAR(v_min_timestamp, 'YYYY-MM-DD HH24:MI:SS'), 'NULL'));

  DBMS_OUTPUT.PUT_LINE('Max Timestamp: ' || NVL(TO_CHAR(v_max_timestamp, 'YYYY-MM-DD HH24:MI:SS'), 'NULL'));

  

  -- Recheck SYSAUX tablespace

  SELECT SUM(bytes) INTO v_total_bytes

  FROM dba_data_files

  WHERE tablespace_name = 'SYSAUX';

  

  SELECT SUM(bytes) INTO v_free_bytes

  FROM dba_free_space

  WHERE tablespace_name = 'SYSAUX';

  

  v_used_bytes := v_total_bytes - NVL(v_free_bytes, 0);

  v_percent_used := ROUND((v_used_bytes / v_total_bytes) * 100, 2);

  

  DBMS_OUTPUT.PUT_LINE('SYSAUX Tablespace After Purge:');

  DBMS_OUTPUT.PUT_LINE('Total Size: ' || ROUND(v_total_bytes / 1024 / 1024 / 1024, 2) || ' GB');

  DBMS_OUTPUT.PUT_LINE('Used Space: ' || ROUND(v_used_bytes / 1024 / 1024 / 1024, 2) || ' GB');

  DBMS_OUTPUT.PUT_LINE('Free Space: ' || ROUND(v_free_bytes / 1024 / 1024 / 1024, 2) || ' GB');

  DBMS_OUTPUT.PUT_LINE('Percent Used: ' || v_percent_used || '%');

  DBMS_OUTPUT.PUT_LINE('Purge completed in ' || 

    ROUND(EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time)), 2) || ' seconds');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

    RAISE;

END;

/