-- How to manage AUDIT trails automatically in 11g before it grows out-of-control overtime --

By Ken Patel

11.2 has AUDIT trail turned on for lot of SYSTEM privileges. But there is no automatic purge job defined out-of-box, not only that SYS.AUD$ is located in SYSTEM tablespace which can cause space management problem unnecessarily.  

11.2 has new package DBMS_AUDIT_MGMT to manage this. one of good thing of this package, is that it allows DBA to setup automatic purge job (not to say, Oracle should have done this out-of-box with 7 days or some good interval just like AWR snapshots). this package also helps moving SYS.AUD$ to SYSAUX or any other tablespace DBA wants.

I think, DBA should manage this as soon as 11.2 Database is setup. Purging takes time if done after few weeks. I would do atleast this much after deploying 11.2 database for controlling audit trails ( Documentation has more details on various other package calls as well as different argument and their significance).

1.

-- this block initializes the clean up as well as moves SYS.AUD$ to SYSAUX tablespace

BEGIN

DBMS_AUDIT_MGMT.INIT_CLEANUP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

DEFAULT_CLEANUP_INTERVAL => 12 );

END;

/

2.

-- manual clean up

BEGIN

  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

   USE_LAST_ARCH_TIMESTAMP    =>  TRUE );

END;

/

2.

-- creating automatic purge job

BEGIN

  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

   AUDIT_TRAIL_PURGE_INTERVAL  => 12,

   AUDIT_TRAIL_PURGE_NAME      => 'Sys_Audit_Purge_JOB',

   USE_LAST_ARCH_TIMESTAMP     => TRUE );

END;

Ken is a Senior Database Engineer at Comcast NETO DBE&O Solutions Engineering Team.