Oracle 11g — Audit Enabled by Default, But What About Purging?

Posted in: Technical Track

If you have created a new Oracle 11g database using DBCA and opted to use by default 11g’s enhanced security settings or, at least, the audit setting, then you risk the unlimited growth of the SYSTEM SYSAUX (thanks Eduardo Legatti) tablespace that hosts the audit trail table SYS.AUD$. I realized that while reviewing the slides of my presentation on 11g’s new features, a few of which covered security enhancements.

During my presentation at the TOUG meeting later that day, I mentioned that concern, and Mohamed El-Shafie from Oracle quickly noticed that there is no auto-purge. I promised to have another look at the maintenance tasks in 11g to confirm that, and indeed, the audit trail is not purged automatically when auditing is enabled by default.

Here is a quick remedy — scheduling an audit trail maintenance job.

First create a PL/SQL procedure that will accept a number of days to keep. It rounds down to the beginning of the day. I like to dump a few diagnostic messages to alert.log when my maintenance procedures are running, so I included that here as well. (There was a typo in purge data calculation — thanks to Nial for catching it.

create or replace procedure purge_audit_trail (days in number) as
  purge_date date;
  purge_date := trunc(sysdate-days);
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
                        purge_date || ' started');
  delete from aud$ where ntimestamp# < purge_date;
  dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' || 
                        purge_date || ' has completed');

Then let’s schedule a new maintenance job using Job Scheduler, which appeared first in 10g. Oracle 11g includes the default maintenance windows group, MAINTENANCE_WINDOW_GROUP, and we will use that to run the purge.

    job_name => 'AUDIT_PURGE',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin purge_audit_trail(31); end;',
    schedule_name => 'MAINTENANCE_WINDOW_GROUP',
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Audit Trail Purge',
    auto_drop => FALSE,
    enabled => TRUE);

During the job run you will see the following in the alert.log:

Tue Jul 01 15:18:02 2008
AUDIT: Purging Audit Trail until 30-JUN-08 started
AUDIT: Purging Audit Trail until 30-JUN-08 has completed

The good news, in a nutshell — Oracle 11g includes default audit settings that should be enabled if you have even a tiny thought about security in your environment. But don’t stop there. Make sure that you implement some purging strategy to avoid unlimited growth of the audit trail.

If you want to go further, and/or if you require the retention of years of audit information, consider exporting the data or moving it to your enterprise auditing repository. This will avoid a significant burden on the SYSTEM SYSAUX (thanks Eduardo Legatti) tablespace.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

23 Comments. Leave new

I guess this (and Audit Vault) is what was behind the introduction of the DBMS_AUDIT_MGMT package …


Actually, this is probably a better link

I know it’s AV documentation, but I think patches are available for various RDBMS versions.


Should “trunc(sysdate-1)” read “trunc(sysdate-days)”?

I’m liking that writing to the alert log idea, very neat.

Alex Gorbachev
July 4, 2008 11:38 am


Interesting package. Thanks for mentioning it Doug. Let me know if you find more details on whether it’s available without DV. Well, I might as well check it one day if I have time.


Good catch mate! Thanks for the correction. It’s fixed now.


It’s Patch number 6989148 for RDBMS I knew I’d seen it somewhere – it was in the AV release notes.

There are a few new scripts in $ORACLE_HOME/rdbms/admin :-


It also mentions that they will be included in but I don’t know what the situation is with (they don’t exist on my laptop db and the patch doesn’t mention I saw a reference to a new solution to moving AUD$ being supplied for 9.2 and up, so I think it will eventually be just a standard db package.



I am not a DBA person but I have read somewhere about auto purge mechanism for audit data in Oracle 11g and its called Flashback data Archive, based on retention policy set, is that right?


Eduardo Legatti
May 25, 2009 1:24 pm

Hi Alex,

Just a little correction … If I’m not wrong, the SYS.AUD$ table is hosted under the SYSTEM tablespace (not SYSAUX).


Alex Gorbachev
May 26, 2009 1:28 am

@Eduardo: Hm… yes — don’t know why I wrote SYSAUX that time. We often move AUD$ to SYSAUX as part of database creation so that might what it has been on the database I happened to check. Fixed the post — thanks for correction.

@Rohit: Missed your question but in general terms — yes. But I don’t think I clarified anything for you with this — I’d rather hit the docs.

Purging Audit Data « ORAganism
May 30, 2009 7:22 am

[…] implement something to purge the audit data after 90 days from some Oracle databases I found a nice blog post from Alex Gorbachev. The post provides a procedure for keeping X days of records in SYS.AUD$ and […]


Hi,what about on older version 9i database.
I have delete previlege on AUD$, but I can’t delete using where timestamp#<xxxx. It gives me ORA-01031: However when I delete all records or using rownum < xx, it works fine. here is my statement

delete from SYS.AUD$ where timestamp# < trunc(sysdate,’MONTH’)
Error at line 1
ORA-01031: insufficient privileges

I tried using userid = ‘xxx’ in the where clause instead of timestamp#, they don’t work either.

any ideas?

Alex Gorbachev
June 9, 2009 10:48 pm

@Praveen: from the top of my head, I couldn’t see why this would be an issue in 9i. I don’t have a sand-box with 9i database at the moment but I would think it should work just fine.

Can you select from AUD$?


@Alex , you wrote a while back about purging AUD$ data using slick pls/sql script(s) anyway did you find out if the dbms_audit_mgmt package is available without licensing the Vault ? otherwise by purging the AUD$ via homemade scripts, will this cleanup the OS xml files , I havent tested yet, but looking for insight from experienced folk.



Oracle Database Vault option shouldn’t have anything to do with this package. At least, I don’t see it mentioned in the licensing guide.

Oracle Audit Vault is actually a separate product by itself and dbms_audit_mgmt package is part of it according to the documentation of Audit Vault itself.


Any info about oracle application auditing – Audit Trail growth..whats the strategy to purge the audit data?


Alex Gorbachev
November 24, 2009 5:15 pm

@Andy: There is no silver-bullet I’m afraid. Every application has its own audit requirements, retention policies and data access patterns.


Auto purging Audit records is a bad decision. Audit should only be purged with human intervention. Audit records are very important information which needs to be reviewed carefully and then any action needs to be taken.
The better solution is to restrict the number of audits happening by disabling auditing on objects which are not important.

Alex Gorbachev
December 17, 2009 1:01 pm

@Mehul: I do not agree. There should be an established policy for audit records retention and in many cases, this retention is not unlimited. How these policies should be setup is another question.

The point of the post is that even if you don’t intentionally enable auditing (thus, you might not have an explicit policy) there are some auditing records collected by default and this is already a limited set.


What can you say regarding making SYS.AUD$ table as partitioned table ?

Alex Gorbachev
January 19, 2010 11:05 pm

@Uri: I know it works for number of people just fine. Though, it does pose a question on supportability by Oracle. Strictly speaking, it’s not supported.

I guess it’s the balance between how much overhead is caused by purging it vs danger of running unsupported configuration (though, Oracle Support would probably never notice).

If you collect way too much data and purging it produces huge overhead, I’d question Audit strategy but never say never.

Purge Audit Logs Oracle Data base 11g « Daily Raaga
March 15, 2012 5:23 am
Altemir Soares
August 7, 2012 12:50 pm


If you’ve realized that so late, then your aud$ should be very big. So, it would be better to do a loop, performing some commits, while deleting the aud$ table. Looks like this :

create or replace procedure purge_audit_trail (days in number) as
purge_date DATE;
CURSOR cr_aud(pdate IN DATE) IS
FROM aud$
where ntimestamp# < pdate;
purge_date := trunc(SYSDATE-days);
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' started');
FOR st_aud IN cr_aud(purge_date) loop
DELETE FROM aud$ WHERE ROWID=st_aud.rid;
IF mod(cr_aud%rowcount, 1000) = 0 THEN
end if;
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' has completed');

Effet caché de la database 11G – Augmentation du volume des traces « EASYTEAM LE BLOG
December 13, 2012 9:29 am

[…] Si vous êtes en version 11.1 ,  créer votre propre tache et inspirez vous pour cela du très bon blog de nos amis de Pythian ici […]

Pablo Sinisgalli
August 2, 2013 2:06 pm

I remember an old metalink note Note:72460.1 saw : Moving AUD$ to another tablespace and adding triggers to AUD$
but you may have problems with logical standby that need the AUD$ in the SYSTEM tablespace and/or inside SYS schema… see : DBA_REGISTRY show Packages and Types as INVALID and DBMS_INTERNAL_LOGSTDBY has compile errors (Doc ID 337119.1)
DBA_REGISTRY show Packages and Types as INVALID and DBMS_INTERNAL_LOGSTDBY has compile errors (Doc ID 337119.1)



Leave a Reply

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