Oracle数据库SYSAUX表空间空闲空间无法回收如何解决？

失败操作原因说明 #

• 尝试1报错ORA-03297：SYSAUX数据文件的高水位线（HWM）高于你设置的5000M，文件尾部仍有已使用的数据块，无法直接截断
• 尝试2报错ORA-12916：SHRINK SPACE语法仅支持临时表空间，不适用于SYSAUX这类永久表空间
• 尝试3报错ORA-32773：ALTER TABLESPACE ... RESIZE仅支持大文件（BIGFILE）表空间，SYSAUX默认是小文件表空间，该语法不适用

正确回收操作步骤 #

步骤1：查询SYSAUX空间占用组件 #

SYSAUX默认存储AWR快照、优化器统计信息、审计日志等系统组件数据，先定位占空间最高的组件：

SELECT occupant_name, schema_name, space_usage_kbytes/1024 space_usage_mb
FROM v$sysaux_occupants
ORDER BY space_usage_mb DESC;

通常占用最高的是AWR快照、历史优化器统计信息两类数据。

步骤2：清理过期/不必要的历史数据 #

清理超期AWR快照

先查询现存快照的ID范围：

SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;

清理指定范围的快照，例如清理ID小于1000的所有快照：

BEGIN
  dbms_workload_repository.drop_snapshot_range(
    low_snap_id => 1,
    high_snap_id => 1000,
    dbid => (SELECT dbid FROM v$database)
  );
END;
/

也可以调整AWR快照保留周期，例如设置保留7天：

BEGIN
  dbms_workload_repository.modify_snapshot_settings(
    retention => 7*24*60,
    interval => 60
  );
END;
/

清理过期优化器统计信息

例如清理30天之前的统计信息：

BEGIN
  dbms_stats.purge_stats(sysdate - 30);
END;
/

如果开启了统一审计，还可以清理过期审计日志：

BEGIN
  dbms_audit_mgmt.clean_audit_trail(
    audit_trail_type => dbms_audit_mgmt.audit_trail_all,
    use_last_arch_timestamp => TRUE
  );
END;
/

步骤3：查询数据文件最小可收缩大小 #

清理完成后，查询SYSAUX数据文件的最高使用块，计算可resize的最小值：

SELECT CEIL(MAX(block_id + blocks) * value / 1024 / 1024) min_resize_mb
FROM dba_extents, v$parameter
WHERE name = 'db_block_size'
  AND tablespace_name = 'SYSAUX'
  AND file_id = (SELECT file_id FROM dba_data_files WHERE tablespace_name = 'SYSAUX' AND file_name LIKE '%SYSAUX01.DBF');

返回的min_resize_mb就是该数据文件可收缩到的最小值，你可以设置大于等于该值的大小。

步骤4：执行数据文件resize操作 #

使用步骤3得到的最小可收缩值，执行resize命令，例如查询得到最小可收缩到2800M，就执行：

ALTER DATABASE DATAFILE '你的SYSAUX01.DBF的完整路径' RESIZE 2800M;

注意事项 #

1. 操作前建议对数据库做全量备份，避免误操作导致系统组件损坏
2. 不要手动MOVE、DROP SYSAUX表空间内的系统对象，可能导致数据库核心功能异常
3. Oracle 18XE有最大12G用户数据的限制，SYSAUX空间回收后不会突破该限制

内容的提问来源于stack exchange，提问作者Thomas Carlton