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;
/