Move Database Audit Trail to a Different Tablespace

1.      Create new tablespace to hold the audit trail.

SQL>create tablespace audit_data datafile 'E:\HISPL\oradata\HISPL\DATAFILE\audit_date.DBF' size 200M;

2.      Truncate sys.aud$ to accelerate movement to the new tablespace (Optional).

Ensure that the size of aud$ can fit the created tablespace otherwise try to delete some of it or just truncate it and that would accelerate the movement.

SQL> truncate table sys.aud$;

3.      Initializes the audit management infrastructure for all audit trails with one week.

SQL> BEGIN IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP'); DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 24*7); else dbms_output.put_line('Cleanup for STD was already initialized'); end if; end; /

4.     Move the standard audit 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_DATA') ; end; /

5.     Specify a timestamp of 7 days ago for the standard audit trail.

Begin DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, last_archive_time => sysdate - 7); end; /

6.     Purge all records older than 7 days.

BEGIN DBMS_AUDIT_MGMT.DROP_PURGE_JOB (AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ'); -- exception -- when others then -- null; end; / BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB ( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_PURGE_INTERVAL => 24, AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ', USE_LAST_ARCH_TIMESTAMP => TRUE ); END; /

7.     Call DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP regularly to advance prompt the last archive TS, Audit Vault will do this for you automatically.

Create or replace procedure set_archive_retention (retention in number default 7) as begin DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, last_archive_time => sysdate - retention); end; /

8.     Schedule automatic advancement of the archive timestamp (Optionally).

Begin   DBMS_SCHEDULER.disable('advance_archive_timestamp');   DBMS_SCHEDULER.drop_job('advance_archive_timestamp'); -- exception -- when others then -- null; end; / BEGIN    DBMS_SCHEDULER.create_job (    job_name => 'advance_archive_timestamp',    job_type => 'STORED_PROCEDURE',    job_action => 'SET_ARCHIVE_RETENTION',    number_of_arguments => 1,    start_date => SYSDATE,    repeat_interval => 'freq=daily' ,    enabled => false,    auto_drop => FALSE);    dbms_scheduler.set_job_argument_value     (job_name =>'advance_archive_timestamp',      argument_position =>1,      -- one week, you can customize this line:      argument_value => 7);    DBMS_SCHEDULER.ENABLE('advance_archive_timestamp'); End; / BEGIN     DBMS_SCHEDULER.run_job (job_name => 'advance_archive_timestamp',     use_current_session => FALSE); END; /