侧边栏壁纸
博主头像
与晚风述往事博主等级

万般皆下品,唯有读书高。

  • 累计撰写 149 篇文章
  • 累计创建 29 个标签
  • 累计收到 7 条评论

目 录CONTENT

文章目录

Oracle 最简单的查询表空间使用率

与晚风述往事
2021-09-24 / 0 评论 / 0 点赞 / 264 阅读 / 3,676 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2021-09-24,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

引言

介于Oracle内部的数据存储结构,在Oracle数据库的管理中,我们需要定期去检查表空间的使用情况,以确认是否需要新增数据文件。然而,我们在写SQL语句的时间,都可以去喝个下午茶了(吐个嘈!),主要原因是语句比较长,像这样:

SELECT A.TABLESPACE_NAME,      
	     FILENUM,   
	     TOTAL "TOTAL (MB)",  
	     F.FREE "FREE (MB)",
	     TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%", 
	     TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",    
	     ROUND(MAXSIZES, 2) "MAX (MB)"
FROM (SELECT TABLESPACE_NAME,          
	           COUNT(FILE_ID) FILENUM,        
	           SUM(BYTES / (1024 * 1024)) TOTAL,          
	           SUM(MAXBYTES) / 1024 / 1024 MAXSIZES      
	    FROM DBA_DATA_FILES       
	    GROUP BY TABLESPACE_NAME) A,     
	    (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE     
	    FROM DBA_FREE_SPACE      
	    GROUP BY TABLESPACE_NAME) F
WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME

如果上面这个你觉得还行的话,你再看看这个:

select a.TABLESPACE_NAME,
to_char(a.表空间当前值,'fm9999999999999990.00')||'G' 表空间当前值,
to_char(b.表空间当前剩余值,'fm9999999999999990.00')||'G' 表空间当前剩余值,
to_char(a.表空间最大值,'fm9999999999999990.00')||'G' 表空间实际最大值,
to_char(a.表空间自动扩展最大值,'fm9999999999999990.00')||'G' 表空间自动扩展最大值,
to_char(round((a.表空间最大值-a.表空间当前值)+b.表空间当前剩余值),'fm9999999999999990.00')||'G' 实际剩余值,
to_char(round((((a.表空间最大值-a.表空间当前值)+b.表空间当前剩余值)/a.表空间最大值)*100,2),'fm9999999999999990.00')||'%' 实际剩余值比率,
to_char(round(((a.表空间当前值-b.表空间当前剩余值)/a.表空间最大值)*100,2),'fm9999999999999990.00')||'%' 实际使用值比率
from (
select tablespace_name,
round(sum(bytes/1024/1024/1024),2) as 表空间当前值,
round(sum(maxbytes/1024/1024/1024),2) as 表空间自动扩展最大值,
round(sum((case when autoextensible='YES' THEN MAXBYTES 
            when autoextensible='NO' THEN bytes end)/1024/1024/1024),2) as 表空间最大值 from dba_data_files a
group by tablespace_name)a
left join 
(SELECT TABLESPACE_NAME, round(SUM(BYTES /1024/1024/1024),2) 表空间当前剩余值    
      FROM DBA_FREE_SPACE      
      GROUP BY TABLESPACE_NAME) b
on a.TABLESPACE_NAME=b.tablespace_name

看完是不是就觉得长了,并且还伴随着疑问,这是谁写的,我怎么没在网上见到过?(嘿嘿,是俺写的,你当然没见过了)

如此长的sql,看着就头大。服务器能够复制还好,不用手动敲sql命令,如果服务器无法复制,瞬间脑海里一条黑线,那么有没有什么办法能避免写这么长的sql就查询到表空间使用率呢?

嘿嘿,没有,欸,大哥,干啥呢,别拿凳子,我左翻翻,右翻翻,找到了!Oracle静态性能视图已经提供了表空间使用率的查询,我们不必再关联这么多的静态性能视图(DBA_开头)去查询,直接使用dba_tablespace_usage_metrics就可以,视图中已经自动计算了表空间的使用率。什么?就只是查询视图?还是单个视图?这不是,洒洒水啦([得意])!

DBA_TABLESPACE_USAGE_METRICS

Oracle提供了dba_tablespace_usage_metrics视图,官方文档是这样描述的,DBA_TABLESPACE_USAGE_METRICS描述了所有类型表空间的表使用指标,包括永久、临时和撤消表空间(UNDO,中文翻译成撤消估计好多人都看不懂了,包括我自己)。

从官方文档上看,这个视图包含所有类型的表空间使用率,它比我们自己写的查询语句好多了。欸!这不是废话吗?不好的话,Oracle也不会发布这个视图了。话不多说,我们直接看视图的定义字段,它有四个字段,分别为:

  • TABLESPACE_NAME 表空间名称
  • USED_SPACE 表空间已使用的值,以数据块为单位
  • TABLESPACE_SIZE 表空间最大值,以数据块为单位
  • USED_PERCENT 表空间已使用的百分比

以上就是这个视图的所有字段(这里指的是能用来查询的字段,而不包括视图中的关联逻辑字段哦!)。

从字段的定义来看,我们在使用中重点需要关注USED_SPACE和TABLESPACE_SIZE,毕竟它的单位不是bytes,而是数据块(db_block_size)的个数。因此,我们在使用视图时,要先查询单个数据块的大小,查询单个数据块大小的语句如下:

select value,description from v$parameter where name='db_block_size';

query_tablespace_1
从上图来看,我这个实例(数据库)查询出来的值是8192,它的单位是bytes。我们得到数据块的大小,就可以使用dba_tablespace_usage_metrics视图查询数据库的表空间使用率了。

DBA_TABLESPACE_USAGE_METRICS的使用

在上面我们得到了单个数据块的大小,在使用时就直接输出的时候将used_space和tablespace_size的结果再乘以数据块的大小就可以了。虽然写起来非常的简单,但是呢,我们本着能复制就决不敲语句的原则(唉呀!就我自己是这样子啦!),直接将常用的几个语句写出来,方便各位大哥复制(不能这么说,应该说是方便我自己复制啦!)。

查询表空间使用率(以byte为单位)

select
	tablespace_name ,
	used_space*8192 ||' Bytes' used_space ,
	tablespace_size*8192 ||' Bytes' tablespace_size ,
	used_percent
from dba_tablespace_usage_metrics

查询表空间使用率(以KB为单位)

select
	tablespace_name ,
	trim(to_char(used_space*8192/1024 , '9999999990.99')) ||' KB' ,
	trim(to_char(tablespace_size*8192/1024 , '9999999990.99')) ||' KB' ,
	used_percent 
from dba_tablespace_usage_metrics

查询表空间使用率(以MB为单位)

select
	tablespace_name ,
	trim(to_char(used_space*8192/1024/1024 , '99990.99')) ||' MB' used_space ,
	trim(to_char(tablespace_size*8192/1024/1024 , '99990.99')) ||' MB' tablespace_size ,
	used_percent
from dba_tablespace_usage_metrics

查询表空间使用率(以GB为单位)

select 
	tablespace_name ,
	trim(to_char(used_space*8192/1024/1024/1024 , '99990.99'))||' GB' as used_space ,
	trim(to_char(tablespace_size*8192/1024/1024/1024 , '99990.99')) ||' GB' as tablespace_size ,
	used_percent 
from dba_tablespace_usage_metrics

0

评论区