Audit Trail Purging (11g Release 2 Only)

As you know Oracle Audit write log to AUD$, FGA_LOG$ tables and OS files, XML files. Day by day these tables and directorys grow in large size. In pre 11g Release 2 you must manual delete or truncate tables and delete OS files, XML files periodically.
In 11g Release 2 you have 1 build-in package to do this easily.
First you must initialize purge process:

begin     sys.dbms_audit_mgmt.init_cleanup(         audit_trail_type     => sys.dbms_audit_mgmt.audit_trail_all,         default_cleanup_interval    => 30*24     ); end;

You can see your purge process by below query:

select * from DBA_AUDIT_MGMT_CLEANUP_JOBS

Next, to perform the actual purge:

begin   dbms_audit_mgmt.clean_audit_trail(     audit_trail_type =>  dbms_audit_mgmt.audit_trail_all   ); end;

When the initial purge is complete, you may want to set up an automated process to execute this periodically. You will need to create a DBMS Scheduler Job as shown below:

begin   dbms_audit_mgmt.create_purge_job (     audit_trail_type    => dbms_audit_mgmt.audit_trail_all,     audit_trail_purge_interval  => 7*24,     audit_trail_purge_name => 'all_audit_trails_job'   ); end;

Since the above SQL performs a DELETE operation, it is not very conducive to a large delete operation. You may want to break it into chunks:

For Database Audit trails begin  dbms_audit_mgmt.set_audit_trail_property(   audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std,   audit_trail_property       => dbms_audit_mgmt.db_delete_batch_size,   audit_trail_property_value => 100000); end; For FGA begin  dbms_audit_mgmt.set_audit_trail_property(   audit_trail_type           => dbms_audit_mgmt.audit_trail_fga_std,   audit_trail_property       => dbms_audit_mgmt.db_delete_batch_size,   audit_trail_property_value => 100000); end;

You can recheck your config:

select * from dba_audit_mgmt_config_params;

Then using a special package you will move the AUD$ table to the new tablespace:

begin   dbms_audit_mgmt.set_audit_trail_location(     audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std,     audit_trail_location_value  => 'AUDIT_TS'   ); end;

To move the FGA_LOG$ table to this tablespace:

begin   dbms_audit_mgmt.set_audit_trail_location(     audit_trail_type  => dbms_audit_mgmt.audit_trail_fga_std,     audit_trail_location_value  => 'AUDIT_TS'   ); end;

List of values for audit_trail_type parameter:

Parameter	Purpose: to remove:
dbms_audit_mgmt.audit_trail_aud_std	The normal AUD$ audit train the database
dbms_audit_mgmt.audit_trail_fga_std	The FGA_LOG$ table, for Fine Grained Auditing
dbms_audit_mgmt.audit_trail_db_std	Both normal and FGA audit trails
dbms_audit_mgmt.audit_trail_os	The OS file audit trails
dbms_audit_mgmt.audit_trail_xml	The XML audit trail files
dbms_audit_mgmt.audit_trail_files	Both the OS and XML audit trails
dbms_audit_mgmt.audit_trail_all	All of the above