本实例主要针对 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);
来源: http://www.linuxidc.com/Linux/2018-06/152911.htm