Purging a Database Audit Trail AUD$

1. Purging a Subset of Records from the Database Audit Trail

   You can manually remove records from the database audit trail tables. This method can be useful if you want to remove a specific subset of records. You can use this method if the database audit trail table is in any tablespace, including the SYSTEM tablespace.
   For example, to delete audit records that were created later than the evening of February 28, 2009 but before March 28, 2009, enter the following statement:
   

   DELETE FROM SYS.AUD$
      WHERE NTIMESTAMP# > TO_TIMESTAMP ('28-FEB-09 09.07.59.907000 PM') AND
      NTIMESTAMP# < TO_TIMESTAMP ('28-MAR-09 09.07.59.907000 PM');
   

   Alternatively, to delete all audit records from the audit trail, enter the following statement:
   

   DELETE FROM SYS.AUD$;
   

   Only the user SYS or a user to whom SYS granted the DELETE privilege on SYS.AUD$ can delete records from the database audit trail.
   
   Note:

   If the audit trail is full and connections are being audited (that is, if the AUDIT SESSION statement is set), then typical users cannot connect to the database because the associated audit record for the connection cannot be inserted into the audit trail. In this case, connect as SYS with the SYSDBA privilege, and make space available in the audit trail. Remember that operations by SYS are not recorded in the standard audit trail, but they are audited if you set the AUDIT_SYS_OPERATIONS parameter to TRUE.
   After you delete the rows from the database audit trail table, the freed space is available for reuse by that table. (The SYS.AUD$ table is allocated only as many extents as are necessary to maintain current audit trail records.) You do not need to do anything to make this space available to the table for reuse. If you want to use this space for another table, then follow these steps:
   
   
   Move the AUD$ table to an auto segment space managed tablespace.
   For example:
   

   BEGIN
     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
      (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
      audit_trail_location_value => 'USERS');
   END;
   /

2. Run the following statements:
   

   ALTER TABLE SYSTEM.AUD$ ENABLE ROW MOVEMENT;
   ALTER TABLE SYSTEM.AUD$ SHRINK SPACE CASCADE;
   

3. If you must move the AUD$ table back to the SYSTEM tablespace, then run the following statement:
   

   BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
     (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
     audit_trail_location_value => 'SYSTEM');
   END;
   /
   

If you want to both delete all the rows from the database audit trail table and free the used space for other tablespace objects, use the TRUNCATE TABLE statement. For example:

TRUNCATE TABLE SYS.AUD$;

Note:

SYS.AUD$ and SYS.FGA_LOG$ are the only SYS objects that can ever be directly modified.

 

 

Reference :

http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG473

http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php