http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_audit_mgmt.htm#ARPLS241
SCRIPT: Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt ( Doc ID 1362997.1 )
Here is an example job to purge audit records from AUD$:
== The AUD$ cleanup should be executed only once ===
begin
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 24);
end;
/
---set the last archive timestamp 180 days ago to purge the records older than 180 days
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - 180);
end;
/
----Create Daily archive timestamp job
begin
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => sysdate-180)); END;',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
enabled => TRUE,
comments => 'Create an archive timestamp'
);
end;
/
begin
sys.dbms_scheduler.enable( '"SYS"."DAILY_AUDIT_ARCHIVE_TIMESTAMP"' );
end;
/
-----Create Daily Purge Job
begin
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
If you don't want to keep audit records you can manually purge all audit records from AUD$:
====Manual purge=====
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate-1,
rac_instance_number => 1);
END;
/
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
Audit Vault can be integrated with dbms_audit_mgmt to automatically purge audit records from AUD$ once they are inserted in AVDF repository, this is explained over here:
http://docs.oracle.com/cd/E37100_01/doc.121/e27776/plugin_specific.htm#SIGAD41209
SCRIPT: Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt ( Doc ID 1362997.1 )
Here is an example job to purge audit records from AUD$:
== The AUD$ cleanup should be executed only once ===
begin
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 24);
end;
/
---set the last archive timestamp 180 days ago to purge the records older than 180 days
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate - 180);
end;
/
----Create Daily archive timestamp job
begin
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => sysdate-180)); END;',
start_date => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
enabled => TRUE,
comments => 'Create an archive timestamp'
);
end;
/
begin
sys.dbms_scheduler.enable( '"SYS"."DAILY_AUDIT_ARCHIVE_TIMESTAMP"' );
end;
/
-----Create Daily Purge Job
begin
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
If you don't want to keep audit records you can manually purge all audit records from AUD$:
====Manual purge=====
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate-1,
rac_instance_number => 1);
END;
/
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
Audit Vault can be integrated with dbms_audit_mgmt to automatically purge audit records from AUD$ once they are inserted in AVDF repository, this is explained over here:
http://docs.oracle.com/cd/E37100_01/doc.121/e27776/plugin_specific.htm#SIGAD41209
