Purging audit records - Oracle At the end of each financial year, audit team asks for audit records for samples dates in a year as per their methodology and ideally we are supposed to provide them. (Say, give them list of schemas dropped in a particular month or modifications made for a schema in a month)

Now, im in a situation I need to save the audit data for one complete year, is there anyway to purge the audit data on a monthly basis and at the same time provide the necessary info by the end of the year. The reason I need to purge is because of the audit file size which grows to a huge size.

What choices do I have? I do not have license to Oracle Audit Vault as well.
Archiving the Database Audit Trail

Scheduling an Automatic Purge Job for the Audit Trail

Manually Purging the Audit Trail

Basically you copy the audit trail to another table, e.g:

INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...; 


You can just export and drop these tables, then import them when you need to access audit records.

Then you set the last archive timestamp, e.g:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =&gt;  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME    =&gt;  '2009-05-28 06:30:00.00'   
   RAC_INSTANCE_NUMBER  =&gt;  0 );
END;
/


Finally you initialize cleanup and schedule a purge or run it manually:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            =&gt; DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL    =&gt; 12 );
END;
/

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            =&gt; DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
   AUDIT_TRAIL_PURGE_INTERVAL  =&gt; 12,
   AUDIT_TRAIL_PURGE_NAME      =&gt; 'Standard_Audit_Trail_PJ',
   USE_LAST_ARCH_TIMESTAMP     =&gt; TRUE );
END;
/


Manual:

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