How to purge SYS.AUD$ table in Oracle

AUD$ table is used in oracle to store the auditing information.

Auditing can be enabled for CREATE SESSION, LOGON, LOGOFF, SELECT, INSERT, DELETE statement
You can enable auditing only when the AUDIT_TRAIL parameter is set to either DB or DB_EXTENDED value. You can check the parameter value as

sqlplus / as sysdba
show parameter AUDIT_TRAIL

11g forwards AUDIT_TRAIL is activated by default and it is set to DB
AUD$ table can grow over time if no purging is scheduled for that
Let’s take a look at the How to purge AUD$ table in Oracle

How to purge AUD$ table in Oracle

(1) First of all, we need to find out the tablespace where AUD$ is stored

SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$')
ORDER BY table_name;

(2) We need to check the size of the table also

SQL> SELECT SUM(BYTES)/1024/1024/1024,tablespace_name FROM DBA_SEGMENTS WHERE SEGMENT_NAME='AUD$';

(2) If this is stored in SYSTEM Tablespace, we need to move it to SYSAUX tablespace as per Oracle standard. We can do this using the below statement

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUDIT_TS');
END;
/

If the AUD$ table is large, this will take a lot of time. Once the statement is completed. Please verify the location of AUD$ again

SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$')
ORDER BY table_name;

If the data is huge, then this AUD$ movement will take enormous, we can below statement in that case. This should be executed during off-peak hours

(i) CREATE TABLE backup_aud$ PARALLEL 24 AS SELECT * from sys.aud$;

(ii) truncate table aud$;

(iii) Now move the table using standard package
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUDIT_TS');
END;
/



(iv)
SQL>insert into /*+ append parallel(p,16) */   aud$ p select * from backup_aud$;
SQL>commit;


(v) drop table backup_aud$;

(3) Now initialize the cleaning job. Note if you have not moved your table to SYSAUX earlier, then it will move it

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;
/

(4) Check the status of initialization

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;
/

(5) Check the audit mgmt configuration parameters:

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20

SELECT * FROM dba_audit_mgmt_config_params;

(6)After doing all the above steps, we are now ready for purging.

If you want to completely empty the AUD$ table, you can use the below statement

BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);
END;
/

If you want to do purging till the last 30 days, then we will need to set the last archive timestamp using the below statement

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-30);
END;
/

Then check the timestamp using the below query

SQL> COLUMN audit_trail FORMAT A20
SQL> COLUMN last_archive_ts FORMAT A40
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

Now you can do the purging till the last 30 days using the command

BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/

(7) Now the last step talks about one-time purging. Now if you want to schedule it daily then it, then you need to do two things
(a) Create a DBMS_SCHEDULER job to set the last archive timestamp daily
(b) Create the purge job for purging AUD$

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'aud_purging_advance',
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 => 'Advance last archive timestamp');
END;
/

DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_purge_interval => 24 ,
audit_trail_purge_name => 'PURGE_DB_AUD_TABLE',
use_last_arch_timestamp => TRUE);
END;
/

(8) How to verify if the purge is running successfully

select /*+ parallel(a,8) */ min(ntimestamp#) from sys.aud$ a;

(9) If the purge is taken using the above steps, we can increase the delete threshold by using the below steps

SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL

SQL> SQL>
SQL> begin
dbms_audit_mgmt.set_audit_trail_property (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_property => dbms_audit_mgmt.db_delete_batch_size,
audit_trail_property_value => 300000);
end;
/ 



SQL> SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE';


PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT CLEAN BATCH SIZE 300000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL

(10). To deconfigure the audit management infrastructure run the DEINIT_CLEANUP procedure.

BEGIN
  DBMS_AUDIT_MGMT.deinit_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL);
END;
/

Hope you like this detailed content on How to purge the AUD$ table in Oracle

How to purge SYS.AUD$ table in Oracle

How to purge AUD$ table in Oracle

1 thought on “How to purge SYS.AUD$ table in Oracle”

Leave a Comment Cancel Reply

How to purge AUD$ table in Oracle

Related Posts

1 thought on “How to purge SYS.AUD$ table in Oracle”

Leave a Comment Cancel Reply