Hi,
I will explain How to Delete or Purge Audit Trail Table and Records in Oracle Database in this article.
Delete or Purge Audit Trail Table and Records
If you don’t know What is the Audit and How to audit an Oracle database, read the following before this.
Purge aud$ table
Sys.aud$ table is growing day by day if you enable auditing. Sometimes this table size may be One or More Terabyte size.
You can move Audit trail tables to new tablespaces instead of system tablespaces as follows.
If you don’t need historical Auditing data, you should delete the old records or purge this table.
If your company needs security log then you can archive this table, then purge this table again.
Truncate SYS.AUD$ Table
You can purge this table as follows.
TRUNCATE TABLE SYS.AUD$;
You can create a scheduler job for this task to perform it everytime.
Firstly create a Program or Procedure that will purge the AUD$ table as follows.
BEGIN SYS.DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'SYS.PURGE_AUD_TABLE' ,program_type => 'PLSQL_BLOCK' ,program_action => 'BEGIN execute immediate ''TRUNCATE TABLE SYS.AUD$''; END;' ,number_of_arguments => 0 ,enabled => FALSE ,comments => 'Program to clear AUD$ table' ); SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.PURGE_AUD_TABLE'); END; /
Then create a scheduler job that will call this program periodically and will run this program instead of you.
Scheduler job scripts are as follows.
BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'SYS.PURGE_AUD' ,schedule_name => 'SYS.DAILY_PURGE_SCHEDULE' ,program_name => 'SYS.PURGE_AUD_TABLE' ,comments => NULL ); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.PURGE_AUD' ,attribute => 'RESTARTABLE' ,value => FALSE); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.PURGE_AUD' ,attribute => 'LOGGING_LEVEL' ,value => SYS.DBMS_SCHEDULER.LOGGING_OFF); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.PURGE_AUD' ,attribute => 'MAX_FAILURES'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.PURGE_AUD' ,attribute => 'MAX_RUNS'); BEGIN SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.PURGE_AUD' ,attribute => 'STOP_ON_WINDOW_CLOSE' ,value => FALSE); EXCEPTION -- could fail if program is of type EXECUTABLE... WHEN OTHERS THEN NULL; END; SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.PURGE_AUD' ,attribute => 'JOB_PRIORITY' ,value => 3); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.PURGE_AUD' ,attribute => 'SCHEDULE_LIMIT'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.PURGE_AUD' ,attribute => 'AUTO_DROP' ,value => FALSE); SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.PURGE_AUD'); END; /
You can drop the Scheduler job as follows if you don’t need it or if you want to drop it.
BEGIN SYS.DBMS_SCHEDULER.DROP_JOB (job_name => 'SYS.PURGE_AUD'); END; /
You can drop the Program as follows if you don’t need it or if you want to drop it.
BEGIN DBMS_SCHEDULER.DROP_PROGRAM (program_name => 'SYS.PURGE_AUD_TABLE'); END; /
You can enable or disable this job as follows.
BEGIN DBMS_SCHEDULER.ENABLE (name => 'SYS.PURGE_AUD'); END; / BEGIN DBMS_SCHEDULER.DISABLE (name => 'SYS.PURGE_AUD'); END; /
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )