PARAMETER_NAME? ? ? ? ? ? ? ? PARAMETER_VALUE AUDIT_TRAIL
------------------------------ --------------- --------------------
DB AUDIT TABLESPACE? ? ? ? ? ? AUDIT_DATA? ? ? STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE? ? ? 10000? ? ? ? ? STANDARD AUDIT TRAIL1
三、清除审计记录
通过这个过程设定清除间隔
SQL> BEGIN
? 2? ? DBMS_AUDIT_MGMT.init_cleanup(
? 3? ? ? audit_trail_type? ? ? ? => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
? 4? ? ? default_cleanup_interval => 120 /* hours */);
? 5? END;
? 6? /

PL/SQL procedure successfully completed.

-- 下面严验证审计日志清除是否已开启
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
? 2? ? IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
? 3? ? ? DBMS_OUTPUT.put_line('YES');
? 4? ? ELSE
? 5? ? ? DBMS_OUTPUT.put_line('NO');
? 6? ? END IF;
? 7? END;
? 8? /
YES

PL/SQL procedure successfully completed.

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

SEGMENT_NAME? ? ? ? BYTES/1024/1024
------------------- ---------------
AUD$? ? ? ? ? ? ? ? ? ? ? ? ? 1152

SQL> select 'Leshami' As author,'http://blog.csdn.net/leshami' as Blog from dual;

AUTHOR? BLOG
------- ----------------------------
Leshami http://blog.csdn.net/leshami

SQL> select count(*) from AUD$;

? COUNT(*)
----------
? 5908086

SQL> select min(ntimestamp#) from aud$;

MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
20-AUG-14 06.11.09.901253 AM

-- 设定归档间隔
SQL> BEGIN
? 2? DBMS_AUDIT_MGMT.set_last_archive_timestamp(
? 3? audit_trail_type? => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
? 4? last_archive_time => SYSTIMESTAMP-10);
? 5? END;
? 6? /

PL/SQL procedure successfully completed

--查看设定的归档间隔
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL? ? ? ? ? RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL? ? ? ? ? ? 0 09-OCT-15 01.27.17.000000 PM +00:00

--通过调用DBMS_AUDIT_MGMT.clean_audit_trail进行手动清理审计日志
BEGIN
? DBMS_AUDIT_MGMT.clean_audit_trail(
? audit_trail_type? ? ? ? => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
? use_last_arch_timestamp => TRUE);
END;
/

DBMS_AUDIT_MGMT.clean_audit_trail
This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the
SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.

--也可以通过创建一个purge Job来进行清理已归档的历史审计记录
SQL> BEGIN
? 2? ? DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
? 3? ? ? AUDIT_TRAIL_TYPE? ? ? ? ? => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
? 4? ? ? AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
? 5? ? ? AUDIT_TRAIL_PURGE_NAME? ? => 'Daily_Audit_Purge_Job',
? 6? ? ? USE_LAST_ARCH_TIMESTAMP? ? => TRUE
? 7? ? );
? 8? END;
? 9? /

PL/SQL procedure successfully completed.

-- 本次测试使用了job进行清理，注，上面的purge job 并非使用DBMS_SCHEDULER.CREATE_JOB创建
-- 执行job用于清理归档，通过观察，由于redo log size为50MB，切换较为频繁，花费了19分钟
-- 同时伴随有Checkpoint not complete等待事件，可见redo size过小
SQL> exec DBMS_SCHEDULER.RUN_JOB(job_name => 'SYS.DAILY_AUDIT_PURGE_JOB');

PL/SQL procedure successfully completed.

Elapsed: 00:19:26.38

SQL> select count(*) from AUD$;

? COUNT(*)
----------
? ? ? ? 12

--经查看，清理后空间并没有释放? ? ? ?
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';