2.dba_tablespace_usage_metrics 查看表空间使用率

 

DBA_TABLESPACE_USAGE_METRICS  

 

 

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)		Tablespace name
USED_SPACE	NUMBER		Total space consumed by the tablespace,in database blocks
TABLESPACE_SIZE	NUMBER		Total size of the tablespace,in database blocks
USED_PERCENT	NUMBER		Percentage of used space,as a function of the maximum possible tablespace size

 

从官网的说明来看，通过视图DBA_TABLESPACE_USAGE_METRICS可以很方便的查看各类型表空间的使用情况，包括永久、临时和undo表空间。但是，通过这个视图查询到的结果，和传统的SQL脚步查询到的结果不一致，而且相差很大。

 

 

select text from dba_views where view_name='DBA_TABLESPACE_USAGE_METRICS';
TEXT
-----------------------------------------------------------------
SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     t.contents$ = 0 and
     bitand(t.flags, 16) <> 16 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     t.contents$ <> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     f.inst_id = param.inst_id and
     param.name = 'undo_tablespace' and
     t.name = param.value and
     f.flag = 6 and                                       <------在12C之前，UNDO表空间在GV $ FILESPACE_USAGE中将其数据文件标记为值6，
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id

 

 

可以看出，DBA_TABLESPACE_USAGE_METRICS查询到的关键数据出自v$filespace_usage视图。

v$filespace_usage视图，11g官方文档的说明如下：

 

 

Column	Datatype	Description
TABLESPACE_ID	NUMBER	ID of the tablespace to which the file belongs
RFNO	NUMBER	Relative file number of the file
ALLOCATED_SPACE	NUMBER	Total allocated space in the file
FILE_SIZE	NUMBER	Current file size
FILE_MAXSIZE	NUMBER	Maximum file size
CHANGESCN_BASE	NUMBER	SCN base of the last change to the file
CHANGESCN_WRAP	NUMBER	SCN wrap of the last change to the file
FLAG	NUMBER	Flags for file attributes

 

 

  

根据以上分析，可以得出以下结论：

1、DBA_TABLESPACE_USAGE_METRICS的USED_SPACE是已经分配的空间，对应 V$FILESPACE_USAGE 的ALLOCATED_SPACE字段。

2、DBA_TABLESPACE_USAGE_METRICS的 TABLESPACE_SIZE对应V$FILESPACE_USAGE的 FILE_MAXSIZE字段（而不是FILE_SIZE）。

  注意：这里对应的是最大值。如果数据文件是自动增长的，那么，对于8k的block，这里的最大值就是32G，

      也就是通过DBA_TABLESPACE_USAGE_METRICS视图查询显示的4194302个blocks。

3、 对于非自动扩展的表空间，使用DBA_TABLESPACE_USAGE_METRICS视图，与传统脚本使用的DBA_DATA_FILE和DBA_FREE_SPACE查询的结果是一致的。

4、对于自动扩展的表空间，DBA_TABLESPACE_USAGE_METRICS视图查询的结果就不准确了，还要使用传统的方法查询。

 

5. 统计UNDO表空间时 ，在12c之前UNDO表空间在GV$FILESPACE_USAGE中将其数据文件标记为值6，并且它是以此方式唯一标识的表空间。

    但是在12.1时，添加undo文件时如果在创建过程中，指定存储路径该数据文件仍标记为6，但不指定路径时，根据MOS错误28821847，这些文件标记为14。

    因此，即使DBA_TABLESPACE_USAGE_METRICS报告UNDO表空间，它也仅报告第一个数据文件的信息：

6. 在12.2中 所有新创建的undo 数据文件都标记为6，但是12.2中DBA_TABLESPACE_USAGE_METRICS，不在记录undo信息

7. 因此,最好查询DBA_DATA_FILES和DBA_FREE_SPACE来监视UNDO使用，因为DBA_TABLESPACE_USAGE_METRICS无法在12.1.0.x中提供可靠的数据，并且在12.2.0.x中绝对不提供数据。       Oracle可能会更改视图定义以反映UNDO表空间文件的实际标志设置，但是就目前而言，最好查询已知包含相关且准确的数据的视图（DBA_DATA_FILES，DBA_FREE_SPACE）。

 

8.在19c中测试 所有新创建的undo 数据文件都标记为10，更改为当前undo表空间时，flag 变改为14

 

https://www.databasejournal.com/features/oracle/trouble-with-oracles-dbatablespaceusagemetrics-for-the-undo-tablespace.html

#19C 中的   DBA_TABLESPACE_USAGE_METRICS
CREATE FORCE VIEW "SYS"."DBA_TABLESPACE_USAGE_METRICS" ("TABLESPACE_NAME", "USED_SPACE", "TABLESPACE_SIZE", "USED_PERCENT") AS
  SELECT  t.name,
        tstat.kttetsused,
        tstat.kttetsmsize,
        (tstat.kttetsused / tstat.kttetsmsize) * 100
  FROM  sys.ts$ t, x$kttets tstat
  WHERE
        t.online$ != 3 and
        t.bitmapped <> 0 and
        t.contents$ = 0 and
        bitand(t.flags, 16) <> 16 and
        t.ts# = tstat.kttetstsn
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     t.contents$ <> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     f.inst_id = param.inst_id and
     param.name = 'undo_tablespace' and
     t.name = param.value and
     f.flag = 6 and                              <---19c的undo 表空间数据文件的flag为14 , 
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#

     <---19c的undo 表空间数据文件的flag为14 , 新创建的undo 数据文件 flag 为10， 
         当吏用以下命令改改当前undo表空间时，alter system set undo_tablespace='UNDOTEST'; 注意大写。
         UNDO表空间数据文件的flag改为14
      

 

 

 

 

# 自定义DBA_TABLESPACE_USAGE_METRICS_2 过程：
CREATE FORCE VIEW "SYS"."DBA_TABLESPACE_USAGE_METRICS_2" ("TABLESPACE_NAME", "USED_SPACE", "TABLESPACE_SIZE", "USED_PERCENT") AS
  SELECT  t.name,
        tstat.kttetsused,
        tstat.kttetsmsize,
        (tstat.kttetsused / tstat.kttetsmsize) * 100
  FROM  sys.ts$ t, x$kttets tstat
  WHERE
        t.online$ != 3 and
        t.bitmapped <> 0 and
        t.contents$ = 0 and
        bitand(t.flags, 16) <> 16 and
        t.ts# = tstat.kttetstsn
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, v$filespace_usage f
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     t.contents$ <> 0 and
     f.flag = 6 and
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#
union
 SELECT t.name, sum(f.allocated_space), sum(f.file_maxsize),
     (sum(f.allocated_space)/sum(f.file_maxsize))*100
     FROM sys.ts$ t, gv$filespace_usage f, gv$parameter param
     WHERE
     t.online$ != 3 and
     t.bitmapped <> 0 and
     f.inst_id = param.inst_id and
     param.name = 'undo_tablespace' and
     t.name = param.value and
     f.flag = 14 and                             
     t.ts# = f.tablespace_id
     GROUP BY t.name, f.tablespace_id, t.ts#
     
 #PDB中 每个PDB都要单独创建

 

 

 

 

 

来自为知笔记(Wiz)