Oracle SYSAUX 表空间减肥

SYSAUX 表空间在默认条件下如果不做任何配置和清理，随着时间的推移会膨胀的越来越大，最后变成一个胖子！

SYSAUX 表空间做为 SYSTEM 表空间的辅助表空间，主要存放 EM 相关的内容以及表统计信息，AWR 快照，审计信息等。 当 SYSAUX 表空间过大，占用空间比重过高时会出现 ORA-03113: end-of-file on communication channel 等问题

这时就要想办法给 SYSAUX 表空间减肥了。

1. 修改统计信息的保持时间

• 查询当前统计信息保留时间

SELECT dbms_stats.get_stats_history_retention FROM dual;

如果没修改过的话，默认为 31 天。

• 修改统计信息保留时间

exec dbms_stats.alter_stats_history_retention(7);

修改为保留 7 天。

• 验证是否修改成功

select dbms_stats.get_stats_history_retention from dual;

2. 修改AWR快照的保存时间

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL  => 60,
                                                    RETENTION => 10080,
                                                    TOPNSQL   => 100);
END;

3. 删除AWR快照

• 查询最最小和最大快照ID

SELECT MIN(SNAP_ID), MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT; 
SELECT SNAP_ID, SNAP_ID, DBID FROM DBA_HIST_SNAPSHOT; 

• 删除快照

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(LOW_SNAP_ID  => <MIN(SNAP_ID)>,
                                               HIGH_SNAP_ID => <MAX(SNAP_ID)>,
                                               DBID         => <DBID>);
END;

4. 增加 SYSAUX 表空间

直接增加表空间对应的物理文件（DBF文件）大小
首先要找到对应的 DBF 文件：

然后修改其大小：

ALTER DATABASE DATAFILE '/home/oracle/app/oradata/orcl/sysaux01.dbf'
RESIZE 100M;