手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle表空间SYSAUX使用率很高解决案例

[日期:2018-06-19] 来源:Linux社区  作者:rangle [字体: ]

本实例主要针对Oracle表空间饱满问题处理方法做个步骤分享。

 一、告警信息

收到zabbix告警信息,表空间 SYSAUX 使用率>95%%,系统表空间sysaux使用率超过了95%。

二、处理步骤

1.登录具体数据库做相应的数据库空间使用率查询

set line 200;
set pagesize 20000;
set feedback off;
col tablespace_name for a20;
col c_free_percent for a12;
col c_used_percent for a12;
col m_free_percent for a12;
col m_USED_PERCENT for a12;
select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)  m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent
from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f
where d.tablespace_name=f.tablespace_name
order by c_free_percent ;

2.查询表空间对应的对象占用情况

select OWNER,segment_name,segment_type,PARTITION_NAME,bytes/1024/1024/1024 Size_GB from dba_segments  where tablespace_name='SYSAUX' order by Size_GB desc
 

3.根据具体大对象做排查,对可以清理的相关数据清理

根据上述SQL查到的大对象主要是

1    SYS    WRH$_LATCH_CHILDREN        WRH$_LATCH__1153813778_29290    TABLE PARTITION    29.927734375
2    SYS    WRH$_LATCH_CHILDREN_PK     WRH$_LATCH__1153813778_29290    INDEX PARTITION    14.984375
3    SYS    WRH$_ACTIVE_SESSION_HISTORY  WRH$_ACTIVE_1153813778_29290    TABLE PARTITION    3.6474609375
4    SYS    WRH$_SQLSTAT            WRH$_SQLSTA_1153813778_29290    TABLE PARTITION    1.2529296875

WRH$_LATCH_CHILDREN 表示快照使用的,其中分区1153813778是DBID, 29290是快照ID

查看29290的快照ID是什么时间的

select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id;

select snap_id, begin_interval_time from sys.dba_hist_snapshot where snap_id=29290

 4.清空分区WRH$_LATCH__1153813778_29290

select * from  WRH$_LATCH_CHILDREN partition ( WRH$_LATCH__1153813778_29290);
 
alter table WRH$_LATCH_CHILDREN truncate partition WRH$_LATCH__1153813778_29290;

5.清理后表空间查看

select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)  m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percent
from  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) f
where d.tablespace_name=f.tablespace_name  and f.tablespace_name='SYSAUX'order by c_free_percent ;

三、脚本附录

1.表空间segment大小查询

select OWNER,segment_name,PARTITION_NAME,segment_type,bytes/1024/1024/1024 Size_GB from dba_segments  where tablespace_name='SYSAUX' order by Size_GB desc

2.表空间使用率查询

set line 200;
set pagesize 20000;
set feedback off;
col tablespace_name for a20;
col c_free_percent for a12;
col c_used_percent for a12;
col m_free_percent for a12;
col m_USED_PERCENT for a12;
select d.tablespace_name,round(d.MB_current_Bytes,2) Curr_Size_MB,round(f.free_mb_bytes,2) Free_Szie_MB,round(d.MB_maxbytes,2) MAX_Size_MB,round((f.free_mb_bytes/d.MB_current_Bytes)*100,2)  c_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_current_Bytes,4)*100 || '%' c_used_percent,round(((d.MB_maxbytes-d.MB_current_Bytes+f.free_mb_bytes)/d.MB_maxbytes)*100,2)  m_free_percent,round((d.MB_current_Bytes-f.free_mb_bytes)/d.MB_maxbytes,4)*100 || '%' m_used_percentfrom  (select tablespace_name,sum(bytes/1024/1024) MB_current_Bytes,sum(maxbytes/1024/1024) MB_maxbytes from dba_data_files group by tablespace_name ) d,(select tablespace_name,sum(bytes/1024/1024) free_mb_bytes from dba_free_space group by tablespace_name) fwhere d.tablespace_name=f.tablespace_name order by c_free_percent ;

3.查看快照ID、查看快照设置信息、设置快照信息

select snap_id, begin_interval_time from sys.dba_hist_snapshot order by snap_id;select * from DBA_HIST_WR_CONTROL;begin
    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention =>43200,interval =>30, topnsql =>'MAXIMUM');
end;
/

4.统计信息清理

exec dbms_stats.purge_stats(systimestamp -11);

Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址https://www.linuxidc.com/Linux/2018-06/152911.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款