How to Delete or Purge Audit Trail Table and Records ( Purge sys.aud$ table ) in Oracle Database

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 )

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *