SYSAUX 减肥

SYSAUX表空间在默认条件下你如果不做任何配置，随着时间的推移，会膨胀的越来越大！SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等

一、查询SYSAUX表空间内各个分类项目占存储空间的比重

col "Item" for a30
col "Move Procedure" for a50
col "Schema" for a20
set linesize 999
set pagesize 999
SELECT OCCUPANT_NAME "Item",
       SPACE_USAGE_KBYTES / 1048576 "Space Used (GB)",
       SCHEMA_NAME "Schema",
       MOVE_PROCEDURE "Move Procedure"
  FROM V$SYSAUX_OCCUPANTS
 ORDER BY 1

二、修改统计信息的保持时间

1. 查询统计信息保留时间
   SQL> select dbms_stats.get_stats_history_retention from dual;
   GET_STATS_HISTORY_RETENTION
   ---------------------------
                            31
2. 修改保留统计信息为7天
   SQL> exec dbms_stats.alter_stats_history_retention(7);
   PL/SQL procedure successfully completed.
3. 验证
   SQL> select dbms_stats.get_stats_history_retention from dual;
   GET_STATS_HISTORY_RETENTION
   ---------------------------
                             7

三、修改AWR快照的保存时间

修改为7天(7*24*60),每小时收集一次

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

四、删除AWR快照

1. 查询最最小和最大快照ID
   

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

2. 删除快照
   

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