sysaux 过大如何清理释放空间

Usage and Storage Management of SYSAUX tablespace occupants SM/AWR, SM/ADVISOR, SM/OPTSTAT and SM/OTHER ( Doc ID 329984.1 )

按照下面的方法进行处置：

首先通过手动的方式，删除老旧的 optimizer statistics 相关信息。

SQL> exec dbms_stats.purge_stats (DBMS_STATS.PURGE_ALL)

手动删除之后，您可以根据需要减少保存期间。

・现在保存期间的确认方法

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
默认是 31 天。

・变更保存期间的方法

(例) 变更保存期间为10天

SQL> connect /as sysdba
SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (10);

※ 如果是RAC环境的话，需要在所有的节点上执行。

另，
使用下面的命令来删除老旧的 optimizer statistics 相关信息，内部使用的
TRUNCATE 语句。一般来说不会有任何的风险。建议您可以在业务空闲的时候
执行这个语句。

SQL> exec dbms_stats.purge_stats (DBMS_STATS.PURGE_ALL)

OPTSTAT 相关联的 Segment 都是以 WRI$_OPTSTAT, I_WRI$_OPTSTAT 或者 OPTSTAT 开头的。
根据您提供的信息，SM/OPTSTAT 里面占比比较大的都是 OPTSTAT 相关的一些内部 INDEX。
SYSAUX 一直在慢慢变大的原因，应该是在日常的运行过程中产生的索引碎片化所导致的。

(4) Space usage by non-AWR components (> 500K)

COMPONENT MB SEGMENT_NAME SEGMENT_TYPE

NON_AWR 24,429.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
NON_AWR 16,192.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
NON_AWR 12,500.0 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
NON_AWR 6,884.0 SYS.I_WRI$_OPTSTAT_H_ST INDEX
......

--------#########################################################
这个时候虽然删除了数据，但空间还没有回收，如何回收空间呢？
没有释放空间是因为“purge_stats”用delete的方式删除数据，虽然数据没了，但是HWM还没降下来，查看OPTSTAT使用哪些表，然后降低其高水位即可。
SQL> SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';

再结合如下sql判断哪个表大，然后就move哪个表
SQL> select a.table_name,a.num_rows from dba_tables a where a.tablespace_name='SYSAUX' and a.table_name like '%OPTSTAT%';

再用如下语句查出相关表的索引，因为move表，索引会失效，需要重建索引
SQL> select i.index_name,i.table_name,i.status,i.table_owner
from dba_indexes i,dba_objects s
where i.table_name=s.object_name
and s.object_name like '%OPTSTAT%'
and s.object_type='TABLE';

降低HWM
alter table WRI$_OPTSTAT_TAB_HISTORY move;
alter table WRI$_OPTSTAT_OPR move;
alter table WRI$_OPTSTAT_IND_HISTORY move;
重建索引
alter index I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online;
alter index I_WRI$_OPTSTAT_TAB_ST rebuild online;
alter index I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;

如果索引rebuild不成功，就要create index
用如下语句生成DDL语句
SQL> set long 4000
SQL> select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_IND_OBJ#_ST','SYS') from dual;

参考文档：[ID 329984.1], [ID 452011.1],[ID 454678.1]