3

Oracle-视图之DBA_TABLESPACE_USAGE_METRICS

 2 years ago
source link: https://blog.51cto.com/baoyw/5659987
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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列显示的是底层存储可用空间量，
##即空闲的总量



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



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;

        

        

            

                report
            

        

    







        
Recommend

        



                

    
About Joyk

    
 





Aggregate valuable and interesting links.

Joyk means Joy of geeK