sysaux表空间增大的几种情况及解决办法
sysaux表空间会因为多种情况而增大,以下介绍几种情况及解决办法
1、由于设置了awr快照基线导致awr无法purge
--查看sysaux表空间内容占用情况
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 ;
--查询sysaux表空间排名前20的大段对象
select owner, segment_name, segment_type, bytes / 1024 / 1024
from (select *
from dba_segments
where tablespace_name = 'SYSAUX'
order by bytes desc)
where rownum < 20;
--查看awr的保留属性
select * from dba_hist_wr_control;
--查看awr的最大、最小快照号
select max(snap_id), min(snap_id) from sys.WRM$_SNAPSHOT;
--查看数据库dbid
select dbid from v$database;
--查看awr相关的基线情况
select * from dba_hist_baseline_details;
--删除指定的基线
EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'test_baseline');
--清理指定范围的awr快照
begin
dbms_workload_repository.drop_snapshot_range(
low_snap_id => 4217,
high_snap_id => 4218,
dbid => 2513064869);
end;
/
2、WRH_ACTIVE_SESSION_HISTORY表未自动purge 参考MOS:WRH_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文档 ID 387914.1)
--检查WRH$_ACTIVE_SESSION_HISTORY表的分区情况
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_SESSION_HISTORY表进行分区,可重复执行
alter session set "_swrf_test_action" = 72;
--检查WRH$_ACTIVE_SESSION_HISTORY表的分区情况
SELECT owner,
segment_name,
partition_name,
segment_type,
bytes/1024/1024/1024 Size_GB
FROM dba_segments
WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
之后oracle内部调用自动清理作业会purge该表的相关分区
3、WRH_SQL_PLAN表未自动purge 参考MOS:How to Purge WRH_SQL_PLAN Table in AWR Repository, Occupying Large Space in SYSAUX Tablespace. (文档 ID 1478615.1)
方法1:可能无效
--查看WRH$_SQL_PLAN表总行数
select count(*) from sys.wrh$_sql_plan;
--查看数据库dbid
SELECT dbid FROM v$database;
--清理WRH$_SQL_PLAN表(官方文档未记录该函数)
exec dbms_workload_repository.purge_sql_details(1000, &dbid);
--查看WRH$_SQL_PLAN表总行数
select count(*) from sys.wrh$_sql_plan;
方法2:
--查看WRH$_SQL_PLAN表记录的最小时间戳
select min(TIMESTAMP) from wrh$_sql_plan;
--手工清理WRH$_SQL_PLAN表,删除dba_hist_snapshot记录时间戳范围外的数据(耗时随数据量增加而延长,注意undo空间使用情况)
delete from wrh$_sql_plan where trunc(TIMESTAMP) < (select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot);
--查看WRH$_SQL_PLAN表总行数
select count(*) from sys.wrh$_sql_plan;
4、WRI$_OPTSTAT_TAB_HISTORY表未自动purge
参考MOS:SYSAUX Grows Because Optimizer Stats History is Not Purged (文档 ID 1055547.1)
原因:由于oracle内部自动purge WRI_OPTSTAT_TAB_HISTORY表的JOB存在5分钟的窗口限制,因此5分钟内未清理完成则JOB失败,该MOS提供的 清理方式未必生效,建议定位到purge WRI_OPTSTAT_TAB_HISTORY表的sql,手工进行清理
--查看sysaux表空间内容占用情况
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 ;
--查询sysaux表空间排名前20的大段对象
select owner, segment_name, segment_type, bytes / 1024 / 1024
from (select *
from dba_segments
where tablespace_name = 'SYSAUX'
order by bytes desc)
where rownum < 20;
--通过awr、ash排查问题时间段自动purge的sql
--追溯sql的执行计划,建立合适的索引后,手工执行purge sql
--降低删除历史信息数据表的高水位线,并重建索引
注:move表前,导出表的所有对象定义(索引、约束、触发器等),move前、后检查所有对象的可用状态是否一致
--检查I_WRI$_OPTSTAT_IND_OBJ#_ST索引状态
select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';
--导出索引定义,最好get_ddl和PLSQL的查看sql定义都导出
select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_TAB_ST','SYS') from dual;
--move WRI$_OPTSTAT_TAB_HISTORY表,降低高水位
alter table WRI$_OPTSTAT_TAB_HISTORY move;
--重建索引
alter index sys.I_WRI$_OPTSTAT_TAB_ST rebuild;
--检查I_WRI$_OPTSTAT_IND_OBJ#_ST索引状态
select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';
本文来自博客园,作者:Eddie小陈,转载请注明原文链接:https://www.cnblogs.com/orachen/p/15878878.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤