How to maintain Auditing housekeeping in oracle 11g

If you enable auditing then the auditing information, (stored in table SYS.AUD$) will start
growing and because is relies on SYSTEM tablespace you might have performance problems in the
future. Auditing housekeeping must be setup.

1. Create a SYSTEM.AUD$_BU table stored in a different tablespace(AUDIT_DATA) where you will
ove all you auditing produced.

[oracle@server1]$ sqlplus /  as sysdba

SQL> CREATE TABLESPACE AUDIT_DATA DATAFILE '/u01/app/oracle/oradata/prim/audit_data_001.dbf' SIZE 100M AUTOEXTEND ON
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT Auto;

2.Create a procedure(Keep_Size_Aud_Log) that moves the rows from SYS.AUD$ to SYSTEM.AUD$_BU
SQL> CREATE OR REPLACE PROCEDURE Keep_Size_Aud_Log
IS
  rowCount NUMBER;
BEGIN
  SELECT COUNT(*) INTO rowCount FROM sys.aud$ ;
  IF rowCount > 0
  THEN
    COMMIT;
    INSERT /*+ APPEND */ INTO SYSTEM.aud$_bu (SELECT * FROM sys.aud$);
 COMMIT;
     EXECUTE IMMEDIATE 'truncate table sys.aud$';
    sys.Dbms_System.ksdwrt (3,'ORA-AUDIT TRAIL: rows moved from SYS.AUD$ to SYSTEM.AUD$_BU');
  END IF;
END Keep_Size_Aud_Log;
/

3. Execute the procedure every day at midnight with a job

SQL> DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SYS.KEEP_SIZE_AUD_LOG;'
     ,next_date  => TO_DATE('23/02/2015 01:00:00','dd/mm/yyyy hh24:mi:ss')
     ,INTERVAL   => 'TRUNC(SYSDATE+1)'
     ,no_parse   => FALSE
    );
END;

Tip: To speed up searching on SYSTEM.AUD$_BU you can create 2 indexes (one on timestamp# and
the other to userid)
SQL> CREATE INDEX SYSTEM.AUD$_BU_TIME_IDX ON SYSTEM.AUD$_BU (TIMESTAMP#) NOLOGGING TABLESPACE
AUDIT_DATA;
Index created.

SQL> CREATE INDEX SYSTEM.AUD$_BU_USERID_IDX ON SYSTEM.AUD$_BU (USERID) NOLOGGING TABLESPACE
AUDIT_DATA;
Index created.