Oracle-视图之DBA_TABLESPACE_USAGE_METRICS

官方文档对于​DBA_TABLESPACE_USAGE_METRICS​​的技术是:​​DBA_TABLESPACE_USAGE_METRICS​​ describes tablespace usage metrics for all types of tablespaces, including permanent, temporary, and undo tablespaces.翻译过来是:DBA_TABLESPACE_USAGE_METRICS 描述了所有类型表空间的表空间使用指标,包括永久表空间、临时表空间和撤消表(UNDO)空间。

使用​DBA_TABLESPACE_USAGE_METRICS​查询表空间使用率。

SYS@b19c01> select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX 188088 941056 19.9869083
SYSTEM 34928 941056 3.71157508
TEMP 1024 941056 .108813928
USERS 344 941056 .036554679

DBA_TABLESPACE_USAGE_METRICS​的视图结构

desc dba_tablespace_usage_metrics
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME VARCHAR2(30)
USED_SPACE NUMBER
TABLESPACE_SIZE NUMBER
USED_PERCENT NUMBER

DBA_TABLESPACE_USAGE_METRICS​​的中文描述。官方文档地址:​​DBA_TABLESPACE_USAGE_METRICS (oracle.com)​

Column

Datatype

NULL

Description

​TABLESPACE_NAME​

​VARCHAR2(30)​

表空间名称

​USED_SPACE​

​NUMBER​

表空间占用的总空间(以数据库块为单位)

对于撤消表空间,此列的值包括过期和未过期撤消段占用的空间。

​TABLESPACE_SIZE​

​NUMBER​

表空间大小,以数据库块为单位

  • 如果表空间包含任何启用了自动扩展的数据文件,则此列显示表空间的基础可用存储空间量。例如,如果当前表空间大小为 1 GB,则其所有数据文件的总最大大小为 32 GB,并且其基础存储(例如,ASM 或文件系统存储)具有 20 GB 的可用空间,则此列的值约为 20 GB。
  • 如果表空间仅包含禁用了自动扩展的数据文件,则此列显示为整个表空间分配的空间,即表空间中所有数据文件的总大小。可以通过查询视图的列来获取数据文件大小。​​BLOCKS​​​​DBA_DATA_FILES​

​USED_PERCENT​

​NUMBER​

已用空间的百分比,作为最大可能表空间大小的函数

大家了解了以上视图​DBA_TABLESPACE_USAGE_METRICS​​的相关结构后,可以很方便的查询数据库的表空间使用率,不必像以前一样,关联好几张视图,写很长的SQL语句,来查询表空间使用率。关联多个视图,查询语句在我的另一篇博客中有描述 ​Oracle-查询表空间使用率_小宝大人的技术博客_51CTO博客​。

那么,如何使用视图​DBA_TABLESPACE_USAGE_METRICS快速而准确 的查询表空间使用率呢?看了以下分析,你就能随便撸啊撸这个视图了。

在官方文档的描述中,大家也发现了,TABLESPACE_SIZE是表空间大小,以数据库块为单位。在使用此视图​DBA_TABLESPACE_USAGE_METRICS直接查询表空间使用率时,所有表空间的TABLESPACE_SIZE都是一样大小,这是什么原因呢?其实,官方文档给出了详细的说明。

我们分两种情况,进行对比分析。

第一种情况是,所有的表空间都禁用了自动扩展数据文件。

在以往的计算中,我们都是以BYTES为单位,计算TABLESPACE_SIZE的大小。即SUM(BYTES)的大小,就是表空间的总大小。但是,在此视图中,以块为单位计算,我们可以查看视图DBA_DATA_FILES Oracle-视图之DBA_DATA_FILES_小宝大人的技术博客_51CTO博客​ 中的BLOCKS,并结合DB_BLOCK_SIZE计算实际大小。

##查询表空间DEFAULT_TBS有两个数据文件,显示都是不可自动扩展
select file_id,
tablespace_name,
bytes / 1024 / 1024 byte_mb,
autoextensible,
maxbytes / 1024 / 1024 maxbyte_mb
from dba_data_files
where tablespace_name = 'DEFAULT_TBS';

FILE_ID TABLESPACE_NAME BYTE_MB AUT MAXBYTE_MB
---------- ------------------------------ ---------- --- ----------
37 DEFAULT_TBS 200 NO 0
46 DEFAULT_TBS 100 NO 0

##直接用视图查询表空间使用率
select *
from dba_tablespace_usage_metrics
where tablespace_name = 'DEFAULT_TBS';

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------ ---------- --------------- ------------
DEFAULT_TBS 312 38400 .8125

##计算表空间占用(消耗)的总空间【USED_SPACE】,以数据库块为单位
select t.tablespace_name,
d.blocks d_blocks,
f.blocks f_blocks,
d.blocks - f.blocks
from dba_tablespaces t,
(select tablespace_name, sum(blocks) blocks
from dba_data_files
group by tablespace_name) d,
(select tablespace_name, sum(blocks) blocks
from dba_free_space
group by tablespace_name) f
where t.tablespace_name = d.tablespace_name
and t.tablespace_name = f.tablespace_name
and t.tablespace_name = 'DEFAULT_TBS';

TABLESPACE_NAME D_BLOCKS F_BLOCKS D.BLOCKS-F.BLOCKS
---------------- ---------- ---------- -----------------
DEFAULT_TBS 38400 38088 312

##计算表空间大小【TABLESPACE_SIZE】,以数据库块为单位
select file_id, blocks
from dba_data_files
where tablespace_name = 'DEFAULT_TBS';

FILE_ID BLOCKS
---------- ----------
37 25600
46 12800

##计算表空间的块数 与以上查询的 TABLESPACE_SIZE 相同
select 25600+12800 from dual;

25600+12800
-----------
38400

##查询单个数据块的大小 8192byte
show parameter db_block_size

NAME TYPE VALUE
------------------- ----------- ------------------------------
db_block_size integer 8192

##查询单个数据块的大小 8192byte,数据库块的大小(以字节为单位)
select value,description from v$parameter where name='db_block_size';

VALUE DESCRIPTION
---------- ------------------------------------------------------------
8192 Size of database block in bytes

##blocks*8192

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

##使用视图查询表空间使用率
set linesize 200;
col tablespace_name for a16;
col used_space for a16;
col tablespace_size for a20;
select tablespace_name,
round(used_space * 8192 / 1024 / 1024, 2) || ' MB' used_space,
round(tablespace_size * 8192 / 1024 / 1024, 2) || ' MB' tablespace_size,
round(used_percent, 2) || '%' used_percent
from dba_tablespace_usage_metrics
where tablespace_name = 'DEFAULT_TBS';

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
---------------- ---------------- -------------------- ------------
DEFAULT_TBS 2.44 MB 300 MB .81%

##通过以上查询计算,如果表空间仅包含禁用自动扩展的数据文件,
##则TABLESPACE_SIZE列显示为整个表空间分配的空间,
##即表空间中所有数据文件的组合大小。
##数据文件大小可以通过查询视图的列来获得。

第二种情况是,如果表空间包含任何启用了自动扩展的数据文件,则TABLESPACE_SIZE列显示表空间的底层存储可用空间量。这里的底层存储,包括ASM 或文件系统存储。

##查看ASM磁盘组中DATA磁盘的可用空间量 7324MB
select name,block_size,total_mb,free_mb from v$asm_diskgroup;

NAME BLOCK_SIZE TOTAL_MB FREE_MB
------------------------------ ---------- ---------- ----------
ARCHIVE 4096 5120 5018
DATA 4096 15360 7324
OCR 4096 5120 4756

##关闭其中一个数据文件的自动扩展
alter database datafile 37 autoextend off;
Database altered.

##查询表空间数据文件自动扩展状态 其中一个是关闭状态
select file_id,
tablespace_name,
bytes / 1024 / 1024 byte_mb,
autoextensible,
maxbytes / 1024 / 1024 maxbyte_mb
from dba_data_files
where tablespace_name = 'DEFAULT_TBS';

FILE_ID TABLESPACE_NAME BYTE_MB AUT MAXBYTE_MB
---------- ---------------- ---------- --- ----------
37 DEFAULT_TBS 200 NO 0
46 DEFAULT_TBS 100 YES 32767.9844

##直接用视图查询表空间使用率
##此时发现,表空间大小 TABLESPACE_SIZE列,发生了变化, 是 937472
select *
from dba_tablespace_usage_metrics
where tablespace_name = 'DEFAULT_TBS';

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
---------------- ---------- --------------- ------------
DEFAULT_TBS 312 937472 .033280994

##查看ASM磁盘组中DATA磁盘的可用空间量 7324MB 换算为块 则正是 937472
select name,block_size,total_mb,free_mb,free_mb*1024*1024/8192 from v$asm_diskgroup;

NAME BLOCK_SIZE TOTAL_MB FREE_MB FREE_MB*1024*1024/8192
----------- ---------- ---------- ---------- ----------------------
ARCHIVE 4096 5120 5018 642304
DATA 4096 15360 7324 937472
OCR 4096 5120 4756 608768

##通过以上查询计算,如果表空间包含任何启用了自动扩展的数据文件,
##那么TABLESPACE_SIZE列显示的是底层存储可用空间量,
##即空闲的总量

所以,快速查询表空间使用率,我们可以这样:

发表评论

相关文章