项目背景
最近接手了一个oracle维护项目,系统环境单机oracle,上游是OGG,下游是ADG,连续两天都收到表空间使用率超过90%的告警。
遇到的情况
1、发现有三个表空间使用过90%,给表空间添加数据文件后,连续两天都收到表空间使用率超过90%的告警。
2、单独统计表空间下的对象发现实际使用的空间不到50%
原因与解决方法
原因
1、业务表的数据增长量确实很大(已排除)
2、表空间的使用率统计有问题(统计方法问题)
因之前给表空间添加数据文件时没有添加autoextend on 参数,数据文件的空间在硬盘上是一次性分配的,而后面我添加的数据文件的初始空间较小并设置了自动扩展参数,之前写的统计语句没有考虑到数据文件自动扩展问题
解决方法
重新实现表空间使用率的统计语句,实现时需要注意以下几种情况
1、表空间对应的数据文件有些是自动扩展的,有些不是
2、表空间对应的数据文件都是自动扩展的。
3、表空间对应的数据文件都不是自动扩展的
实现SQL
select tablespace_name,round(total_space_mb)||'M' total,round(free_space_mb)||'M' free,round(total_space_mb-free_space_mb)||'M' used,round(100*(total_space_mb-free_space_mb)/total_space_mb) used_percent
from (
select
-- 合并计算
decode(t.tablespace_name,null,y.tablespace_name,t.tablespace_name) tablespace_name,
decode(t.tablespace_name,null,y.total_space_mb,t.total_space_mb+nvl(y.total_space_mb,0)) total_space_mb,
decode(t.tablespace_name,null,y.free_space_mb,t.free_space_mb+nvl(y.free_space_mb,0)) free_space_mb
from
(
-- 统计表空间数据文件非自动扩展的情况
select a.tablespace_name,a.total_space_mb,b.free_space_mb from (
select a.tablespace_name ,sum(a.bytes)/1024/1024 total_space_mb
from dba_data_filesa
where a.AUTOEXTENSIBLE='NO'
group by a.tablespace_name) a,
( select tablespace_name , sum(bytes)/1024/1024 free_space_mb
from dba_free_spacewhere file_id in (select file_id from dba_data_filesb where b.AUTOEXTENSIBLE='NO') group by tablespace_name)
b
where a.tablespace_name=b.tablespace_name
) t
full join
(
--统计表空间数据文件自动扩展的情况
select t.tablespace_name,t.total_space_mb,total_space_mb-t.used_space_mb+y.free_space_mb as free_space_mb from (
select m.tablespace_name, sum(m.maxbytes)/1024/1024 total_space_mb ,
sum(m.bytes)/1024/1024 used_space_mb
from dba_data_filesm
where m.AUTOEXTENSIBLE='YES' group by m.tablespace_name) t,
(
select m.tablespace_name, sum(m.bytes)/1024/1024 free_space_mb
from dba_free_spacem
where file_id in (select file_id from dba_data_files
where AUTOEXTENSIBLE='YES' )
group by m.tablespace_name) y
where t.tablespace_name=y.tablespace_name
) y
on t.tablespace_name=y.tablespace_name
) m
GK_PD 196608M 170242M 26366M 13
SYSAUX 32768M 26364M 6404M 20
USERS 32868M 32866M 2M 0
GK 1302528M 627751M 674777M 52
TABLESPACE_BPMUSER 43008M 43006M 2M 0
GKSTG 555008M 151929M 403079M 73
SCGK 409600M 174069M 235531M 58
UNDOTBS_NEW 184320M 182003M 2318M 1
OPENFIRE 20480M 20459M 22M 0
SYSTEM 33792M 13417M 20375M 60
BIGDATA 1443840M 1402478M 41362M 3
问题总结
1、SQL语句书写得冗长,应该有简洁的实现方式。
2、自己给表空间添加数据文件时,没有查看之前的数据文件参数。
3、统计表空间使用率时需要考虑到自动扩展和非自动扩展情况。