AUD$ Table Management

Overview

If auditing is set to DB (default) you need to routinely purge the SYS.AUD$ table or it will cause your SYSTEM tablespace to grow to a gargantuan size. See Auditing.

Display Count

 -- Total Files Created In the Last 30 Days
 SELECT count(*) FROM SYS.AUD$  WHERE ntimestamp# < sysdate-30;

  -- Get Date: 30 Days Ago
 SELECT to_char(sysdate-30,'yyyy.mm.dd') from dual;

Change 30 to whatever number of days you want to retain files in these examples.

Simple Purge Session

 DELETE FROM SYS.AUD$ WHERE ntimestamp# < sysdate-30;

Optimize Session

 -- Get Baseline Values
 COL owner           FORMAT a10
 COL tablespace_name FORMAT a15
 COL bytes           FORMAT 999,999,999,999
 SELECT owner, tablespace_name, bytes/1024/1024 FROM dba_segments 
 WHERE segment_name='AUD$';

 SELECT sum(bytes) as bytes from dba_segments where tablespace_name='SYSTEM';
 SELECT sum(bytes) as bytes from dba_data_files where tablespace_name='SYSTEM';
 SELECT count(*) from sys.aud$;

 -- Move AUD$ Table
 BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
   audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   audit_trail_location_value => 'USERS');
 END;
 /

 -- Purge Table Data
 -- Meh!
 --EXEC DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type =>  
 --dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,default_cleanup_interval => 12);
 --EXEC DBMS_AUDIT_MGMT.clean_audit_trail(audit_trail_type => 
 --DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,use_last_arch_timestamp => FALSE);
 --DELETE FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;

 -- This way is still simpler and less overhead than new way.
 DELETE FROM SYS.AUD$ WHERE ntimestamp# < sysdate-30;
 COMMIT;

 -- Optimize Segments
 ALTER TABLE SYS.AUD$ ENABLE ROW MOVEMENT;
 ALTER TABLE SYS.AUD$ SHRINK SPACE COMPACT;
 ALTER TABLE SYS.AUD$ SHRINK SPACE;

 -- Move Back
 BEGIN
   DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
     audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
     audit_trail_location_value => 'SYSTEM');
 END;
 /

 -- QC
 SELECT owner, tablespace_name, bytes/1024/1024 FROM dba_segments 
 WHERE segment_name='AUD$';

 SELECT count(*) from aud$;
 SELECT sum(bytes) as bytes from dba_segments where tablespace_name='SYSTEM';
 SELECT sum(bytes) as bytes from dba_data_files where tablespace_name='SYSTEM';