Purge the Audit records with truncate or DBMS_AUDIT_MGMT package
Check AUDIT is enabled or disabled
Show parameter audit_trail
NAME TYPE VALUE ------------- ------- -------- audit_trail string DB
Check total no of rows in Audit table
select count(*) TOTAL from sys.aud$;
Check the size of AUD$ table
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB Size" from dba_segments where segment_name='AUD$';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB Size ----- ------------ ------------ --------------- ------- SYS AUD$ TABLE SYSTEM 550
Purge the AUDIT records Manually
--For complete purging the AUDIT table.
TRUNCATE table sys.AUD$;
--Delete all data from AUD$ except keeping last 30 days
--disable the aud$ logging otherwise it generate lot of archive logs
alter table AUD$ nologging;
delete from aud$ where TIMESTAMP# <= sysdate-30;
Purge the Audit records with Package DBMS_AUDIT_MGMT
1. Check the configuration
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SELECT * FROM dba_audit_mgmt_config_params;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ -------------------- -------------------- AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 OS AUDIT TRAIL AUDIT FILE MAX AGE 5 XML AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL DB AUDIT TABLESPACE TS_AUDIT STANDARD AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL
2. Clear the Audit Record initially
--- Clean initially
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 12);
end;
/
Note: If you haven’t moved the AUD$ table out of SYSTEM tablespace, then it the below script will move the AUD$ to SYSAUX tablespace by default.
--Check first step is completed YES
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
--Set the last archive timestamp to remove the audit records lower than value of it. i keep 30 days data for aud$ table.
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-30);
END;
/
--Verify
SELECT * FROM dba_audit_mgmt_last_arch_ts;
For complete empty the AUD$ table from DBMS package
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
For Enable or Disable the Audit at Database Level
--For Enable
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
--For Disable
ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;
Scheduled the Job of Audit purging in DBMS SCHEDULER JOBS
-- Create the scheduler job which purge the AUD$ table daily
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JOB_PURGE_AUDIT_RECORDS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/
-- Select the scheduler job
select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name= ’JOB_PURGE_AUDIT_RECORDS’;
Smart way of Technology 