oracle19c SYSAUX表空间使用率高

今早手机收到一个信息，某客户的19c环境sysaux使用率超过了80%告警了。登录查看下的

SET LINES 120 pagesize 199;
COL OCCUPANT_NAME FORMAT A30;
SELECT * FROM (SELECT OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB FROM V$SYSAUX_OCCUPANTS ORDER BY SPACE_USAGE_KBYTES DESC) WHERE ROWNUM<=5;

OCCUPANT_NAME            SPACE_USAGE_KBYTES
----------------------- -                -----------------
SM/ADVISOR                         11132800
AUDSYS                                 10230208
JOB_SCHEDULER                 2530944
SM/AWR                                 1403136
SM/OPTSTAT                         775168

其实是2个问题

1、解决SM/ADVISOR使用率高问题

参考MOS文档
How To Disable Optimizer Statistics Advisor From 12.2 Onwards (Doc ID 2686022.1)、
How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)，

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

  alter table WRI$_ADV_OBJECTS move;
  alter index WRI$_ADV_OBJECTS_PK rebuild;
  alter index WRI$_ADV_OBJECTS_IDX_01 rebuild;
  alter index WRI$_ADV_OBJECTS_IDX_02 rebuild;

也可以禁用该任务，而不是删除

 EXEC dbms_stats.init_package();

declare
  filter1 clob;
begin
  filter1 := dbms_stats.configure_advisor_rule_filter('AUTO_STATS_ADVISOR_TASK','EXECUTE', NULL,'DISABLE');
END;
/

2、AUDSYS使用率高问题

SYS >  select count(*) from unified_audit_trail;
SYS > exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,use_last_arch_timestamp  =>  FALSE);
 

Unable To Purge All Unified_audit_trail Records where UNIFIED_AUDIT_POLICIES is null and action_name='LOGOFF BY CLEANUP'; (Doc ID 1944147.1)

Bug 17513133 - 'LOGOFF BY CLEANUP' RECORDS NOT PURGED BY DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL