Auditing

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. 

Here is a practical guide, from a 5TByte E-Business Suite, with 50MB auditing produced every day. 

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

CREATE TABLESPACE AUDIT_DATA DATAFILE

  '/filesystem034/oradata/SID/audit_data_001.dbf' SIZE 10000M AUTOEXTEND OFF

LOGGING

PERMANENT

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL;

CREATE TABLE SYSTEM.AUD$_BU TABLESPACE AUDIT_DATA

AS SELECT * FROM SYS.AUD$

WHERE 1=2;

2. Create a procedure(Keep_Size_Aud_Log) that moves the rows from SYS.AUD$ to SYSTEM.AUD$_BU

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

DECLARE

  X NUMBER;

BEGIN

  SYS.DBMS_JOB.SUBMIT

    (

      job        => X

     ,what       => 'SYS.KEEP_SIZE_AUD_LOG;'

     ,next_date  => TO_DATE('27/02/2008 12:35:21','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)

CREATE INDEX SYSTEM.AUD$_BU_TIME_IDX ON SYSTEM.AUD$_BU (TIMESTAMP#)

NOLOGGING TABLESPACE AUDIT_DATA;

CREATE INDEX SYSTEM.AUD$_BU_USERID_IDX ON SYSTEM.AUD$_BU (USERID)

NOLOGGING TABLESPACE AUDIT_DATA;