Posted in 2016

Purge Job for Auditing

There is not much to write about this. It’s a purge job for the audit trail 😀 E: it will not work 😅 i have changed it a little 😅

DECLARE
lat TIMESTAMP := TO_TIMESTAMP('30-06-17 00:00:00.00','DD-MM-YYYY HH24:MI:SS.FF');
BEGIN

dbms_audit_mgmt.init_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std, DEFAULT_CLEANUP_INTERVAL => 1);

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => lat);

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 => 'AUD_PURGE_JOB',
USE_LAST_ARCH_TIMESTAMP => TRUE );

dbms_audit_mgmt.deinit_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std);

END;
/

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'advance_archive_timestamp',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
-- Advance the archive timestamp
dbms_audit_mgmt.init_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std, DEFAULT_CLEANUP_INTERVAL => 1);

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => systimestamp – 120);

dbms_audit_mgmt.deinit_cleanup(AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std);
END; ',
start_date => SYSDATE,
repeat_interval => 'freq=DAILY;BYHOUR=0' ,
enabled => true,
auto_drop => FALSE);
END;
/

Advertisements