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]
