SYSAUX 表空间安全清理技巧(仅供参考，生产环境慎用)

SYSAUX 是 Oracle 数据库的辅助系统表空间，存储大量非核心但关键的元数据（如优化器统计信息、AWR 快照、审计日志、PL/SQL 调试信息等），其膨胀是数据库运维常见问题。清理核心原则是：先定位占用源 → 再安全清理 → 最后配置预防策略，避免直接删除系统数据导致功能异常。

一、第一步：精准定位 SYSAUX 占用 Top 对象

清理前必须先找到 “罪魁祸首”，避免盲目操作。执行以下 SQL 查看各组件的空间占用占比：

-- 查看SYSAUX表空间各组件的空间占用（单位：MB）
SELECT 
  occupant_name "组件名称",
  space_usage_kb/1024 "占用MB",
  space_usage_kb/(SUM(space_usage_kb) OVER ())*100 "占比%",
  schema_name "所属用户",
  move_procedure "官方清理方法"
FROM v$sysaux_occupants
ORDER BY space_usage_kb DESC;

常见高占用组件及说明

表格

二、第二步：按组件安全清理（核心技巧）

针对不同高占用组件，采用 Oracle 官方推荐的清理方法，避免直接 TRUNCATE/DELETE 系统表。

1. 最常用：清理 AWR 快照（占比最高，优先处理）

AWR（自动工作负载仓库）快照是 SYSAUX 膨胀的首要原因，清理需通过官方存储过程，而非直接删表：

（1）查看当前 AWR 配置

-- 查看AWR快照保留期（默认8天，单位：天）和快照间隔（单位：小时）
SELECT 
  snap_interval/3600 "快照间隔(小时)",
  retention/86400 "保留期(天)",
  topnsql "Top SQL采集数"
FROM dba_hist_wr_control;

（2）安全清理过期 AWR 快照

-- 方法1：缩短保留期（自动清理过期快照，推荐）
-- 示例：将保留期改为7天，快照间隔改为1小时（根据业务调整）
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
  retention => 7*24*60,  -- 保留分钟数（7天=7*24*60）
  interval  => 60        -- 快照间隔分钟数（1小时=60）
);

-- 方法2：手动删除指定时间段的快照（紧急清理）
-- 示例：删除2026-01-01之前的快照（dbid需替换为实际值，可查dba_hist_wr_control的dbid）
DECLARE
  l_dbid NUMBER := (SELECT dbid FROM v$database);
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
    low_snap_id  => 1,        -- 起始快照ID（可查dba_hist_snapshot）
    high_snap_id => 1000,    -- 结束快照ID
    dbid         => l_dbid
  );
END;
/

2. 清理优化器统计信息历史（你之前关注的表）

优化器统计信息历史表（WRI$_OPTSTAT_%）是常见占用源，需通过官方包清理，而非直接 TRUNCATE：

-- 查看统计信息历史保留期（默认31天）
SELECT dbms_stats.get_stats_history_retention() "保留期(天)" FROM dual;

-- 步骤1：缩短保留期（示例：改为7天）
EXEC DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);

-- 步骤2：手动清理过期统计信息（核心，安全删除历史数据）
EXEC DBMS_STATS.PURGE_STATS(SYSDATE - 7);  -- 删除7天前的统计信息

-- （可选）若需紧急释放空间，可TRUNCATE（仅建议测试库/确认无风险时）
-- 注意：TRUNCATE前必须备份，且确认不需要恢复历史统计信息
TRUNCATE TABLE sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
TRUNCATE TABLE sys.WRI$_OPTSTAT_HISTGRM_HISTORY;

3. 清理审计日志（AUD$ 表）

若审计功能开启，AUD$ 表会持续膨胀，清理分两步：

-- 步骤1：查看审计日志占用
SELECT COUNT(*) FROM sys.aud$;
SELECT segment_name, bytes/1024/1024 "MB" FROM dba_segments WHERE segment_name='AUD$';

-- 步骤2：安全清理（保留近期日志，示例：保留30天）
DELETE FROM sys.aud$ WHERE ntimestamp# < SYSDATE - 30;
COMMIT;

-- 步骤3：释放高水位线（清理后重置空间）
ALTER TABLE sys.aud$ MOVE;
ALTER INDEX sys.I_AUD1 REBUILD;  -- 重建索引

进阶优化

将 AUD$ 表迁移到非 SYSAUX 表空间（避免占用系统表空间）：

ALTER TABLE sys.aud$ MOVE TABLESPACE USERS;  -- 替换为业务表空间

4. 清理其他低风险组件

• PL/SQL 调试信息：sql-- 清理PL/SQL调试残留数据 EXEC DBMS_DEBUG_JDWP.CLEANUP_OLD_SESSIONS(SYSDATE - 1);
• 数据库升级 / 补丁历史：sql-- 仅清理确认无用的升级日志（谨慎！） DELETE FROM sys.registry$history WHERE action_time < SYSDATE - 90; COMMIT;

三、第三步：清理后释放空间（重置高水位线）

Oracle 执行 DELETE/TRUNCATE 后，表空间的 “高水位线” 不会自动重置，需手动释放：

-- 1. 对单个表释放空间（示例：针对WRI$_OPTSTAT_HISTHEAD_HISTORY）
ALTER TABLE sys.WRI$_OPTSTAT_HISTHEAD_HISTORY SHRINK SPACE CASCADE;

-- 2. 对SYSAUX表空间整体释放（需开启自动段空间管理 ASSM）
ALTER TABLESPACE sysaux SHRINK SPACE;

-- 3. 若表空间为文件系统管理，可收缩数据文件（谨慎！）
ALTER DATABASE DATAFILE '/u01/oracle/oradata/ORCL/sysaux01.dbf' 
  RESIZE 5G;  -- 改为实际需要的大小（需小于当前已用空间+预留）

四、第四步：配置预防策略（避免再次膨胀）

清理后需配置自动策略，从根源避免再次膨胀：

1. AWR 配置：保留期控制在 7-15 天（非核心系统），核心系统不超过 30 天；快照间隔≥1 小时（避免高频采集）。
2. 统计信息清理：通过定时任务自动清理，示例：

-- 创建定时任务：每天凌晨2点清理7天前的统计信息
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'PURGE_OPTSTAT_HISTORY',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.PURGE_STATS(SYSDATE - 7); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE,
    comments        => '自动清理7天前的优化器统计信息'
  );
END;
/

1. 审计日志：若非业务必需，关闭不必要的审计项；或定期归档审计日志到外部存储。
2. 监控告警：配置 SYSAUX 表空间使用率告警（如使用率≥80% 时触发通知）。

五、绝对禁止的高危操作（避坑！）

1. 直接 DROP/TRUNCATE v$sysaux_occupants 中标记为 MANDATORY（强制）的组件；
2. 未备份就清理 WRI$_AWR%、WRI$_OPTSTAT% 等核心系统表；
3. 收缩数据文件时，大小小于当前已用空间（会导致数据文件损坏）；
4. 清理生产库 AWR 快照前，未确认业务不需要历史性能分析数据。

总结

1. SYSAUX 清理核心：先通过 v$sysaux_occupants 定位高占用组件，再按官方方法清理（优先 AWR 和统计信息）；
2. 安全原则：优先用 Oracle 自带存储过程（如 DBMS_WORKLOAD_REPOSITORY、DBMS_STATS），而非直接删表；清理前备份关键数据；
3. 长期优化：配置自动清理策略 + 监控告警，从根源避免表空间膨胀，而非事后补救。