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!

1 comment

1 ping

  1. Eraldo

    Rodrigo ,

    Que excelente artigo sobre expurgo de auditoria , estou implementando auditoria aqui na empresa e é exatamente isso que vou precisar , não sabia dos novos recursos do R11G.

    Muito bom !

    Valeu !!!!

    1. DBA RJ

      Grande abç!

  2. Deyse

    Excelente artigo,

    Estou implementando a auditoria na empresa onde trabalho e este artigo me ajudou bastante.

    Muito Obrigada!

    1. DBA RJ

      Que bom Deyse! Fico grato por ouvir isso! =]

  3. Carlos Roberto da Silva

    Recentemente, instalamos um banco na versão, sendo que “The Unified Auditing” não está habilitado e “The get_stats_history_retention is 7”.
    No tablespace AUDSYS, os objetos “table” CLI_SWP$21f1d8bc$1$1 and “lobsegment” SYS_LOB0000091751C00014$$ estão em constante crescimento.
    Você tem alguma dica de como limitar o crescimentos destes objetos?

    1. DBA RJ

      Oi Carlos,
      As melhores formas de limitar são alterando a quantidade de auditoria que você gera ou aumentando a frequência e diminuindo a retenção para maiores expurgos dos dados históricos.
      Caso não seja possível, outra recomendação é exportar com alguma periodicidade os dados das tabelas de auditorias e limpá-las.

  4. raj

    good documnet. it worked for me in my prod enviroenment.


  5. Tiago teleken

    Muito bom.

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="">