清理SYSAUX表空间

SYSAUX 表空间简介

SYSAUX 表空间是 Oracle 10g 以后引入的一个辅助表空间，用于存放数据库的多种辅助性元数据和管理信息，减轻 SYSTEM 表空间的压力。许多 Oracle 组件（如 AWR、Statspack、LogMiner、Data Mining、Streams、Text、Enterprise Manager 等）都默认使用 SYSAUX 表空间。

收到告警一个 11g 的库 sysaux 表空间不足，上去检查看到使用率达到了 95%，所以接下来就说下如何处理这种情况。

SELECT Upper(F.TABLESPACE_NAME)        "表空间名",
            D.TOT_GROOTTE_MB                "表空间大小(M)",
            D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
            To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
            || '%'                          "使用比",
            F.TOTAL_BYTES                    "空闲空间(M)",
            F.MAX_BYTES                      "最大块(M)"
    FROM  (SELECT TABLESPACE_NAME,
                    Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
                    Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
            FROM  SYS.DBA_FREE_SPACE
            GROUP  BY TABLESPACE_NAME) F,
            (SELECT DD.TABLESPACE_NAME,
                    Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
            FROM  SYS.DBA_DATA_FILES DD
            GROUP  BY DD.TABLESPACE_NAME) D
    WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
    and D.tablespace_name = 'SYSAUX';

占用 SYSAUX 表空间的主要组件

可以通过如下 SQL 查询各组件占用空间情况：

SELECT occupant_name, occupant_desc, space_usage_kbytes
  FROM v$sysaux_occupants
 ORDER BY space_usage_kbytes DESC;

常见占用说明：

• AWR（SM/AWR）：性能历史快照，空间占用大，清理需谨慎。
• LogMiner：日志挖掘，空间异常增长多与大事务或归档相关。
• SM/ADVISOR：统计顾问任务，空间可通过删除任务释放。
• XDB、ORDIM、EM 等：一般不建议手动清理，除非明确不再使用相关功能。

AWR 相关空间管理

1. 查看 AWR 保留期限

SELECT retention FROM dba_hist_wr_control;

如需调整保留天数和快照间隔：

EXECUTE dbms_workload_repository.modify_snapshot_settings (interval => 60, retention => 10080); -- 间隔60分钟，保存7天

2. 清除 AWR 历史数据

先查快照范围：

SELECT snap_id, begin_interval_time, end_interval_time
  FROM SYS.WRM$_SNAPSHOT
 WHERE snap_id = (SELECT MIN(snap_id) FROM SYS.WRM$_SNAPSHOT)
    OR snap_id = (SELECT MAX(snap_id) FROM SYS.WRM$_SNAPSHOT);

清除指定范围快照：

BEGIN
  dbms_workload_repository.drop_snapshot_range(low_snap_id => 16873, high_snap_id => 17083);
END;
/

注意：该操作可能耗时较长，建议业务低峰期执行。

3. 重建 AWR 对象（高风险）

SQL> connect / as sysdba
SQL> @?/rdbms/admin/catnoawr.sql
SQL> @?/rdbms/admin/catawrtb.sql

风险提示：重建 AWR 对象有丢失历史数据风险，生产环境慎用，务必提前备份。

4. 手动清理 WRH$ 表

SELECT DISTINCT 'truncate table '||segment_name||';', s.bytes/1024/1024 AS MB
  FROM dba_segments s
 WHERE s.segment_name LIKE 'WRH$%'
   AND segment_type IN ('TABLE PARTITION', 'TABLE')
   AND s.bytes/1024/1024 > 100
 ORDER BY MB DESC;

LogMiner 数据清理

LogMiner 数据占用 SYSAUX 表空间，常因大事务或归档堆积。可迁移表空间：

SQL> connect / as sysdba
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNRTS');

logmnr_restart_ckpt$ 表过大处理

• 增大 _CHECKPOINT_FREQUENCY 参数，减少检查点频率：

exec dbms_capture_adm.stop_capture('CAPTURE');
begin
  dbms_capture_adm.set_parameter('CAPTURE','_CHECKPOINT_FREQUENCY','1000');
end;
/
exec dbms_capture_adm.start_capture('CAPTURE');

• 缩短检查点保留时间，自动清理元数据：

exec dbms_capture_adm.alter_capture(capture_name =>'STRMADMIN_CAPTURE', CHECKPOINT_RETENTION_TIME=>7);

• 手动释放空间：

alter table system.LOGMNR_RESTART_CKPT$ enable row movement;
alter table system.LOGMNR_RESTART_CKPT$ shrink space;
alter table system.LOGMNR_RESTART_CKPT$ modify lob (CKPT_INFO) (shrink space);
alter table system.LOGMNR_RESTART_CKPT$ disable row movement;

alter index <index name> shrink space;

• 重新编译失效对象：

SQL> @?/rdbms/admin/utlrp.sql

SM/ADVISOR 数据清理

Statistics Advisor 任务（AUTO_STATS_ADVISOR_TASK）可删除以释放空间：

DECLARE
  v_tname VARCHAR2(32767);
BEGIN
  v_tname := 'AUTO_STATS_ADVISOR_TASK';
  DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

删除后建议重组表及索引：

ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;

检查索引状态：

SELECT index_name, status FROM dba_indexes WHERE table_name='WRI$_ADV_OBJECTS';

如遇 ORA-20001 错误，需重新初始化：

SQL> conn sys / as sysdba
SQL> EXEC DBMS_STATS.INIT_PACKAGE();

批量清理 WRI$_ADV_OBJECTS 表

如果数据量太大，执行上面的存储过程会占用大量的回滚段而导致执行失败，这里可以换成truncate方式

CREATE TABLE WRI$_ADV_OBJECTS_NEW AS SELECT * FROM WRI$_ADV_OBJECTS WHERE TASK_ID !=(SELECT DISTINCT ID FROM WRI$_ADV_TASKS WHERE NAME='AUTO_STATS_ADVISOR_TASK');
TRUNCATE TABLE WRI$_ADV_OBJECTS;

INSERT INTO WRI$_ADV_OBJECTS SELECT * FROM WRI$_ADV_OBJECTS_NEW;
COMMIT;

ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

19c 及以上需指定所有列：

INSERT INTO WRI$_ADV_OBJECTS("ID" ,"TYPE" ,"TASK_ID" ,"EXEC_NAME" ,"ATTR1" ,"ATTR2" ,"ATTR3" ,"ATTR4" ,"ATTR5" ,"ATTR6" ,"ATTR7" ,"ATTR8" ,"ATTR9" ,"ATTR10","ATTR11","ATTR12","ATTR13","ATTR14","ATTR15","ATTR16","ATTR17","ATTR18","ATTR19","ATTR20","OTHER" ,"SPARE_N1" ,"SPARE_N2" ,"SPARE_N3" ,"SPARE_N4" ,"SPARE_C1" ,"SPARE_C2" ,"SPARE_C3" ,"SPARE_C4")
SELECT "ID" ,
       "TYPE" ,
       "TASK_ID" ,
       "EXEC_NAME" ,
       "ATTR1" ,
       "ATTR2" ,
       "ATTR3" ,
       "ATTR4" ,
       "ATTR5" ,
       "ATTR6" ,
       "ATTR7" ,
       "ATTR8" ,
       "ATTR9" ,
       "ATTR10",
       "ATTR11",
       "ATTR12",
       "ATTR13",
       "ATTR14",
       "ATTR15",
       "ATTR16",
       "ATTR17",
       "ATTR18",
       "ATTR19",
       "ATTR20",
       "OTHER" ,
       "SPARE_N1" ,
       "SPARE_N2" ,
       "SPARE_N3" ,
       "SPARE_N4" ,
       "SPARE_C1" ,
       "SPARE_C2" ,
       "SPARE_C3" ,
       "SPARE_C4"
FROM WRI$_ADV_OBJECTS_NEW;

其他常见占用及处理

• XDB：如不使用 XML DB，可考虑卸载（需评估依赖）。
• Enterprise Manager（EM）：如不使用 OEM，可考虑清理相关对象。
• Statspack：如已启用 AWR，可考虑停用并清理历史数据。

常见问题与注意事项

• 清理前务必备份数据库或相关表空间。
• truncate、shrink、重建对象等操作可能导致对象失效或性能抖动，建议业务低峰期操作。
• 清理后建议重建相关索引，并检查对象有效性。
• 多租户（CDB/PDB）环境需切换到正确的容器。
• 部分操作（如 drop_snapshot_range）可能耗时较长。

参考资料

• Oracle 官方文档：Managing the SYSAUX Tablespace