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.
Posts
