徐万新之路1. 适用场景
本文适用于以下生产环境问题场景：
- SYSAUX 表空间长期持续增长
- AWR / ASH 数据未按保留策略自动清理
- Optimizer Statistics Histogram 历史数据堆积
- AWR 分区集中在 _0 分区，导致 purge 失效
- 需要精准控制 SNAP_ID 范围，避免误删有效性能数据
2. 操作总体思路
先评估 → 再拆分 → 再验证 → 再精确清理 → 最终收缩表空间
核心原则：
SYSAUX 不能“直接收缩”，只能通过降低 HWM 后 resize 数据文件
3. 清理前评估（基线采集）
3.1 检查 SYSAUX 中 AWR / Optimizer Statistics 占用
conn / as sysdba
@?/rdbms/admin/awrinfo.sql
- 输出文件：awrinfo.lst
- 重点关注：
- Optimizer Statistics History
- AWR Tables
- AWR Indexes
- 该结果必须保留，作为清理前基线
3.2 确认 Optimizer Statistics Histogram 历史跨度
select systimestamp - min(savtime)
from sys.wri$_optstat_histgrm_history;
目的：
- 确认系统中最早的统计信息时间
- 为后续 dbms_stats.purge_stats 设定安全阈值
4. 清理 Optimizer Statistics 历史数据
4.1 按天数清理历史统计信息
exec dbms_stats.purge_stats(sysdate - <保留天数>);
说明：
- <保留天数> 必须小于步骤 3.2 查询结果
- 操作不可回滚
- 不影响当前统计信息，仅清理历史版本
5. AWR / ASH 分区拆分前检查
5.1 检查 ASH 表当前分区结构
set lines 180
col owner for a10
col segment_name for a30
col partition_name for a30
select owner, segment_name, partition_name, segment_type,
bytes/1024/1024/1024 size_gb
from dba_segments
where segment_name = 'WRH$_ACTIVE_SESSION_HISTORY';
典型问题现象：
- 历史数据长期集中在：
WRH$_ACTIVE_<DBID>_0
- 新快照仍持续写入该分区
- 自动 purge 无法触发
5.2 确认分区内 SNAP_ID 分布
select *
from (
select distinct snap_id, dbid
from sys.wrh$_active_session_history
partition(WRH$_ACTIVE_<DBID>_0)
order by snap_id desc
)
where rownum <= 10;
目的：
- 明确该分区 SNAP_ID 上下界
- 为后续拆分和精确清理做准备
6. 手工拆分 AWR 分区（关键步骤）
6.1 触发 AWR 分区拆分
alter session set "_swrf_test_action" = 72;
⚠️ 重要说明（必须写入运维规范）：
- 该操作为隐藏参数行为
- 会对 所有 AWR 分区表 执行一次拆分
- 每执行一次，只拆分一次
- 不需要回退或关闭
- 若需要多次拆分，需重复执行
拆分效果：
- 新分区命名格式：
WRH$_ACTIVE_<DBID>_<SNAP_ID>
- 新生成 SNAP_ID ≥ 分区名后缀值
6.2 拆分后确认分区结构
select owner, segment_name, partition_name, segment_type,
bytes/1024/1024/1024 size_gb
from dba_segments
where segment_name = 'WRH$_ACTIVE_SESSION_HISTORY';
7. 补齐 SNAP，确保分区边界完整
7.1 手工生成快照
exec dbms_workload_repository.create_snapshot();
exec dbms_workload_repository.create_snapshot();
exec dbms_workload_repository.create_snapshot();
原因说明：
- 拆分后存在 SNAP_ID 空洞
- 必须生成新 SNAP 写入新分区
- 否则 SNAP_ID 边界不连续，不利于后续 purge
8. 确认各分区 SNAP_ID 范围
set serveroutput on
declare
cursor cur_part is
select partition_name
from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';
type partrec is record (snapid number, dbid number);
type partlist is table of partrec;
outlist partlist;
begin
dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
dbms_output.put_line('--------------------------- ------- ----------');
for part in cur_part loop
execute immediate
'select min(snap_id), dbid from sys.wrh$_active_session_history
partition ('||part.partition_name||') group by dbid'
bulk collect into outlist;
for i in 1..outlist.count loop
dbms_output.put_line(part.partition_name||' Min '||
outlist(i).snapid||' '||outlist(i).dbid);
end loop;
execute immediate
'select max(snap_id), dbid from sys.wrh$_active_session_history
partition ('||part.partition_name||') group by dbid'
bulk collect into outlist;
for i in 1..outlist.count loop
dbms_output.put_line(part.partition_name||' Max '||
outlist(i).snapid||' '||outlist(i).dbid);
dbms_output.put_line('---');
end loop;
end loop;
end;
/
9. 按 SNAP_ID 精准清理 AWR 数据
exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id => 1,
high_snap_id => 3629,
dbid => 4075230014
);
⚠️ 注意事项：
- 操作不可回滚
- 会同时清理：
- AWR
- ASH
- SQLSTAT
- 必须确认 SNAP_ID 范围完全准确
10. 清理后验证（逻辑空间）
conn / as sysdba
@?/rdbms/admin/awrinfo.sql
对比：
- 清理前 / 清理后 awrinfo.lst
- 确认 AWR / Optimizer Statistics 占用明显下降
11. 手动收缩 SYSAUX 表空间
11.1 确认是否存在可回收空间
select tablespace_name,
round(sum(bytes)/1024/1024/1024,2) total_gb
from dba_data_files
where tablespace_name='SYSAUX'
group by tablespace_name;
select tablespace_name,
round(sum(bytes)/1024/1024/1024,2) used_gb
from dba_segments
where tablespace_name='SYSAUX'
group by tablespace_name;
11.2 收缩 SYSAUX 数据文件
select file_name, bytes/1024/1024/1024
from dba_data_files
where tablespace_name='SYSAUX';
alter database datafile '/u01/oradata/xxx/sysaux01.dbf' resize <目标大小>G;
说明：
- resize 是 唯一真正释放 OS 层空间的方式
- 新大小必须 ≥ 当前 HWM
- 在线执行，不影响业务
12. 风险与注意事项
- 不建议在业务高峰期执行
- resize 建议分多次进行（每次 5–10GB）
- 操作前必须具备可用 RMAN 备份
- 不适用于 SYSTEM 表空间
- CDB/PDB 环境需确认当前容器
13. 总结
通过 手工拆分 AWR 分区 + SNAP_ID 精准清理 + SYSAUX 数据文件 resize，
成功解决了 生产库 SYSAUX 表空间异常增长且无法自动回收的问题。
该方案适用于：
- 长期运行系统
- AWR 数据量巨大
- 自动 purge 机制失效的生产环境
