Oracle 19c 统一审计表清理

zabbix 收到SYSAUX表空间告警超过90%告警，最后面给出的清理方法只适合ORACLE 统一审计表的清理，传统审计表的清理SYS.AUD$不适合，请注意。

SQL> Col tablespace_name for a30
Col used_pct for a10
Set line 120 pages 120
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct 
from (
select tablespace_name, sum(bytes) /1024/1024 as MB 
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB 
from dba_data_files group by tablespace_name) total     
where free.tablespace_name = total.tablespace_name 
order by 4
/SQL> SQL> SQL>   2    3    4    5    6    7    8    9  TABLESPACE_NAME                  TOTAL_MB    USED_MB USED_PCT
------------------------------ ---------- ---------- ----------
BICD                                 8192    1942.25 23.71%
SYSTEM                               4096    1026.75 25.07%
LOGMINER_TBS                           25          1 4%
OGG_DATA                               20          9 45%
UNDOTBS1                             4215     246.38 5.85%
BAKBICD                             13744   10812.44 78.67%
USERS                                5015    4775.38 95.22%
SYSAUX                           55793.98   53137.05 95.24%

查询占用空间的对象

col Item for a20
col Schema for a12
SELECT OCCUPANT_NAME "Item",SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",SCHEMA_NAME "Schema",MOVE_PROCEDURE "Move Procedure"FROM V$SYSAUX_OCCUPANTS WHERE SPACE_USAGE_KBYTES > 1048576 ORDER BY "Space Used (GB)" DESC;
或者
SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(D.BYTES)/1024/1024/1024 SIZE_GB FROM DBA_SEGMENTS D 
WHERE D.TABLESPACE_NAME = 'SYSAUX' 
GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE 
ORDER BY SIZE_GB;
或者
SELECT * FROM (SELECT SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES / 1024 / 1024FROM DBA_SEGMENTSWHERE TABLESPACE_NAME = 'SYSAUX'ORDER BY 4 DESC)WHERE ROWNUM <= 10;

处理方法：
方式1：直接清理全部的审计记录

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  FALSE);
END;
/

方式三：创建定时清理的JOB

The following code can be used to combine purge job & automatic advancement of archive timestamp in one scheduler code.BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'PURGE_UNIFIED_AUDIT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, SYSTIMESTAMP-14);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp  =>  TRUE);
END;',
start_date => '',
repeat_interval => 'freq=daily; byhour=3; byminute=10; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Purge unified audit trail older than 14 days.');
END;
/