DB Wiki

By default, from 11gR1, when a database is created using DBCA, the database auditing parameter will be turned on.

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

This means whenever a connection is made to the database, an entry is logged in the Audit Trail.
Therefore the table sys.aud$ can grow very quickly and on web applications will affect performance

You can turn this off by entering the following as SYS user …

SYS> NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;

Noaudit succeeded.

This still means that failed logins are audited.

SQL below will show everything that has been audited today

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
set linesize 160
col os_username format a20
col userhost format a25

select os_username, username, action, userhost, timestamp 
from dba_audit_trail 
where timestamp > trunc(sysdate)
order by timestamp;

Copy sys.aud$ table to the SYSAUX tablespace

BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    DEFAULT_CLEANUP_INTERVAL => 24
  );
END;
/

Create a job to set the Archive Timestamp. In this case it will be over 30 days

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => 
                   DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-30); END;', 
    start_date => sysdate, 
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24', 
    enabled    =>  TRUE,
    comments   => 'Create an archive timestamp'
  );
END;
/

Create a purge job which will delete all audit entries older than the Archive Timestamp.

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
    AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;
/