环境:
Os:Centos 7
DB:11.2.0.4
1.初始化清理Audit的功能,该命令只有在第一次执行时需要运行,default_cleanup_interval =>72 代表清理周期为72小时(3天).
BEGIN
sys.DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 72);
END;
/
2.查看清除Audit功能是否开启,yes为开启
SET SERVEROUTPUT ON
BEGIN
IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
3.设置需要清理的天数，最后一个数字3代表清理3天前的数据(归档时间大于等于清除时间)
BEGIN
sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-3);
END;
/
4.执行清除,时间长短受数据量大小影响
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
执行改命令看到后台是使用delete的方式删除的
DELETE FROM SYS.AUD$ WHERE DBID = 1510119012 AND NTIMESTAMP# < to_timestamp('2023-10-06 14:08:13', 'YYYY-MM-DD HH24:MI:SS.FF') AND ROWNUM <= 10000;
5.删除一天前的审计日志
BEGIN
sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-1);
END;
/
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/