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.

What is the Database Auditing in Oracle

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.

How to Move Audit Trail tables to New Tablespaces in Oracle

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 )