SYSAUX表空间过大问题
查看 v$sysaux_occupants 确认占用空间对象
select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;
查询结果：
SM/ADVISOR 32741952
通过 dba_segments 进一步确认
select segment_name,owner,tablespace_name,bytes/1024/1024"SIZE(MB)",segment_type from dba_segments where tablespace_name='SYSAUX' order by bytes desc;
查看结果：
WRI$_ADV_OBJECTS SYS SYSAUX 14497 TABLE
WRI$_ADV_OBJECTS_IDX_01 SYS SYSAUX 6907 INDEX
WRI$_ADV_OBJECTS_IDX_02 SYS SYSAUX 5761 INDEX
WRI$_ADV_OBJECTS_PK SYS SYSAUX 4804 INDEX
原因：
因为在 12.2中，引入了新的特性：optimizer statistics advisor。优化器统计信息顾问每天都会在维护窗口运行，auto_stats_advisor_task多次运行，因而会消耗大量sysaux表空间。
解决方案：
1、删除statistics advisor task(auto_stats_advisor_task),删除该任务后就可以释放统计信息顾问产生的数据
declare
v_tname varchar2(32767);
begin
v_tname :='AUTO_STATS_ADVISOR_TASK';
dbms_stats.drop_advisor_task(v_tname);
end;
/
在删除任务的过程中，可能会遇到下面的错误：
ORA-20001:Statistics Advisor: Invalid TaskName For thecurrent user
如果遇到上面的错误，可以先重建 AUTO_STATS_ADVISOR_TASK来解决问题：
SQL>connect as sysdba
SQL>EXEC DBMS_STATS.INIT_PACKAGE();
删除任务后，重新组织表和索引
alter table wri$_adv_objects move;
alter index wri$_adv_objects_idx_01 rebuild;
alter index wri$_adv_objects_idx_02 rebuild;
alter index wri$_adv_objects_pk rebuild;
如果 WRI$_ADV_OBJECTS 记录过多，delete以上会占用较大undo,可以把想要的数据存储在临时表，truncate table WRI$_ADV_OBJECTS,再insert回来。
create table wri$_adv_objects_new tablespace ticket_data
as select * from wri$_adv_objects
where task_id !=(select distinct id
from wri$_adv_tasks
where name='AUTO_STATS_ADVISOR_TASK');
SQL> select count(*)from wri$_adv_objects_new;
COUNT(*)
----------
2968
insert /*+ APPEND */ into wri$_adv_objects
select * from wri$_adv_objects_new;
truncate后查看空间占用：
select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB
from V$SYSAUX_OCCUPANTS
order by SPACE_USAGE_KBYTES DESC;
SM/ADVISOR Server Manageability - Advisor Framework 6.1875
2、缩短任务执行历史的保存时间
确认当前设定的保持期间（ 30天或UNLIMITED）
修改设定的保持期间
可以通过下面的命令，将 EXECUTION_DAYS_TO_EXPIRE修改为15天，即执行历史数据的保存时间为7天：
connect as sysdba
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 15
);
END;
/
3、也可以禁用该任务，而不是删除。
declare
filter1 clob;
begin
filter1 := dbms_stats.configure_advisor_rule_filter('AUTO_STATS_ADVISOR_TASK',
'EXECUTE',
NULL,
'DISABLE');
END;
/