OWNER SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS AUD$ AUDIT_DATA
SQL> @tbs_free.sql
TABLESPACE_NAME USED (MB FREE (MB TOTAL (M PER_FR
------------------------------ -------- -------- -------- ------
AUDIT_DATA 1,153 47 1,200 4 %
SYSAUX 1,143 67 1,210 6 %
SYSTEM 724 1,166 1,890 62 %
-- 从上面的这个查询可以看出，原来位于system表空间的AUD$被迁移到了AUDIT_DATA
-- 相应地AUDIT_DATA表空间已使用增加，而SYSTEM表空间使用率下降
-- 查看审计数据字典配置信息
SQL> col PARAMETER_NAME FOR a30
SQL> col PARAMETER_VALUE FOR a15
SQL> col AUDIT_TRAIL FOR a20
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
2 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
3 WHERE audit_trail = 'STANDARD AUDIT TRAIL';
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,'https://blog.csdn.net/leshami' as Blog from dual;
AUTHOR BLOG
------- ----------------------------
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.