How to purge audit logs on Oracle 11g

https://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG472

Manually Purging the Audit Trail

You can manually purge the audit trail right away, without scheduling a purge job. Similar to a purge job, you can purge audit trail records that were created before an archive timestamp date or all the records in the audit trail.

Note the following about the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure:

• Only the current audit directory is cleaned up when you run this procedure.
• On Microsoft Windows, because the DBMS_AUDIT_MGMT package does not support cleanup of Windows Event Viewer, setting the AUDIT_TRAIL_TYPE property to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS has no effect. This is because operating system audit records on Windows are written to Windows Event Viewer. The DBMS_AUDIT_MGMT package does not support this type of cleanup operation.
• On UNIX platforms, if you set the AUDIT_SYSLOG_LEVEL initialization parameter to a valid value as listed in Oracle Database Reference, then Oracle Database writes the operating system log files to syslog files. If you set the AUDIT_TRAIL_TYPE property to DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, then the procedure only removes .aud files under audit directory (This directory is specified by the AUDIT_FILE_DEST initialization parameter).
• When the AUDIT_TRAIL_TYPE parameter is set to DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, this procedure only cleans up XML audit files (.xml) in the current audit directory. Oracle Database maintains an index file, called adx_$ORACLE_SID.txt, which lists the XML files that were generated by the XML auditing. The cleanup procedure does not remove this file.

For database audit trails, you must initialize the cleanup infrastructure by running the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure, and then purging the database audit trail by using the method described in "Purging a Subset of Records from the Database Audit Trail".

To manually purge the audit trail:

1. Follow these steps under "Scheduling an Automatic Purge Job for the Audit Trail":
   • Step 1: If Necessary, Tune Online and Archive Redo Log Sizes
   • Step 2: Plan a Timestamp and Archive Strategy
   • Step 3: Initialize the Audit Trail Cleanup Operation
   • Step 4: Optionally, Set an Archive Timestamp for Audit Records
   • Step 5: Create and Schedule the Purge Job
   • Step 6: Optionally, Configure the Audit Trail Records to be Deleted in Batches
2. Purge the audit trail records by running the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL PL/SQL procedure.
   For example:

   BEGIN
     DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
      AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      USE_LAST_ARCH_TIMESTAMP    =>  TRUE );
   END;
   /
   

   In this example:
   • AUDIT_TRAIL_TYPE: Enter one of the following values:
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail table, AUD$
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail table, FGA_LOG$
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trail tables
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: Operating system audit trail files with the .aud extension. (This setting does not apply to Windows Event Log entries.)
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML: XML audit trail files
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: Both operating system and XML audit trail files
     • DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: All audit trail records, that is, both database audit trail and operating system audit trail types
   • USE_LAST_ARCH_TIMESTAMP: Enter either of the following settings:
     • TRUE: Deletes audit records created before the last archive timestamp. To set the archive timestamp, see "Step 4: Optionally, Set an Archive Timestamp for Audit Records". The default (and recommended) value is TRUE. Oracle recommends that you set USE_LAST_ARCH_TIMESTAMP to TRUE.
     • FALSE: Deletes all audit records without considering last archive timestamp. Be careful about using this setting, in case you inadvertently delete audit records that should have been deleted.

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.