May 22

Changing AUDIT tablespace and create a purge job in 11g

This post is also available in: Portuguese (Brazil)

Oracle Database 11g comes with some audit options turned on by default, which over time can exhaust the SYSTEM tablespace without the DBA knowledge. However, this same version of database now offers some DBMS packages that can greatly facilitate the life of the administrator who wants to migrate audit to another tablespace or change the retention period of the data. Until version 10gR2, it was necessary to make this change in a more archaic way, which can be checked at Doc ID 1019377.6 on Oracle Support.

To begin, make sure your audit options are turned on.

In my case, I activated for all sys operations and configured to be saved also the run SQL (DB_EXTENDED). This can vary from case to case. Place the desired values ​​and restart your database.

Let’s first find in which tablespace are the audit tables.

Now let’s check if the automatic purge infrastructure process of the audit data has already been initialized.

If it is already enabled, skip to the next step. Otherwise, we will then initialize the infrastructure and set the cleaning interval (not the retention interval) for every 1 day (24 hours).


Note the appearance of the last 2 rows with the value “DEFAULT CLEAN UP INTERVAL” set to 24 hours.
The next step is the creation of a unique tablespace to store audit data:

Now we will move the tables AUD$ and FGA_LOG$ to the new tablespace created. It is recommended that these tables are empty to optimize the process. Truncate if possible or leave your data and wait.

Time to see if the tables were really moved. Check if the values ​​of the parameter “DB AUDIT TABLESPACE” in the first query has been changed.

Now let’s create a new Scheduler that will daily mark all audit records prior to 90 days as filed. Thus, the cleaning process can clean them.

Checking if the job was created.

Finally, we will create the process to daily clean the records that were marked by the previous job.

Checking if the job was created.

Now, I recommend removing all of the default audit options of Oracle and reapplying them only for executions “Whenever Successful“, not commands that execution failed for lack of privileges.


Done, now you no longer need to worry about the uncontrolled growth of your SYSTEM tablespace. The audit is isolated in a dedicated tablespace.

Did you enjoy? Comment or like it. Thanks for visiting!

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">