Auditing housekeeping
                                                 Last update (2008-06-12 00:36:37)
                                                                                                                   Date added (2008-02-27 12:26:05)

Summary
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;
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search