Oracle dba_tablespace_usage_metrics 视图 查看表空间 说明

　　

　　
　　
　　
一．DBA_TABLESPACE_USAGE_METRICS 视图的理论说明
　　
　　群里一朋友说使用dba_tablespace_usage_metrics 视图查看表空间的结果不正确，如下：

　　
　　
　　dba_tablespace_usage_metrics视图在oracle 10g中引入，但未公开。 到了Oracle11g公开了这个视图。因为在Oracle 10g里可以使用这个视图，但是在官网文档里看不到说明，但是在11g的官方文档里可以查看到该视图的说明。
　　
　　Oracle 11g中对这个视图的说明如下：
　　
　　DBA_TABLESPACE_USAGE_METRICSdescribestablespace usage metrics for all types of tablespaces, including permanent,temporary, and undo tablespaces.

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

　　
　　从官网的说明，这个视图很方便，通过DBA_TABLESPACE_USAGE_METRICS视图就可以查看所有类型表空间的使用情况，包括永久，临时和undo 表空间。
　　Oracle的文档也说的不够详细。上面表格中的括号部分Dave的补充。 DBA_TABLESPACE_USAGE_METRICS
　　视图中的USED_SPACE和TABLESPACE_SIZE的单位是block。默认情况下，oracle的每个block 是8k。
　　
　　我们先查看DBA_TABLESPACE_USAGE_METRICS视图的创建代码：
　　
　　SQL> select * from v$version;
　　
　　BANNER
　　--------------------------------------------------------------------------------
　　Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production
　　PL/SQL Release 11.2.0.3.0 - Production
　　CORE11.2.0.3.0 Production
　　TNS for Linux: Version 11.2.0.3.0 -Production
　　NLSRTL Version 11.2.0.3.0 – Production
　　
　　SQL> set pagesize 500
　　SQL> set long 999999
　　SQL> select text from dba_views where view_name='DBA_TABLESPACE_USAGE_METRICS';
　　
　　TEXT
　　--------------------------------------------------------------------------------
　　SELECTt.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
　　t.ts# = f.tablespace_id
　　GROUP BY t.name, f.tablespace_id, t.ts#
　　
　　
　　SQL>
　　
　　通过上面的代码，我们可以看到，DBA_TABLESPACE_USAGE_METRICS说能查询所有类型的表空间，其本质也是进行了3个union。其关键数据还是出自：v$filespace_usage 视图。
　　
　　官网对v$filespace_usage 视图的说明如下：
　　
　　V$FILESPACE_USAGEsummarizes spaceallocation information of each datafile and tempfile.

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字段。 这里对应的是最大值，如果我们的数据文件是自增长的，那么对于8k的block，那么这里的最大值就是32G。也就是最开始显示的4194302个blocks。
　　
　　这个值与我们使用传统的DBA_DATA_FILES和DBA_FREE_SPACE查询的结果就会有很大的出入。
　　
　　/* Formatted on 2013/3/21 20:39:40(QP5 v5.185.11230.41888) */
　　SELECTD.TABLESPACE_NAME,
　　SPACE"SUM_SPACE(M)",
　　BLOCKSSUM_BLOCKS,
　　SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
　　ROUND((1 - NVL(FREE_SPACE,0) /SPACE)* 100, 2) "USED_RATE(%)",
　　FREE_SPACE "FREE_SPACE(M)"
　　FROM( SELECTTABLESPACE_NAME,
　　ROUND (SUM(BYTES)/(1024 * 1024),2) SPACE,
　　SUM (BLOCKS)BLOCKS
　　FROM DBA_DATA_FILES
　　GROUPBYTABLESPACE_NAME) D,
　　( SELECTTABLESPACE_NAME,
　　ROUND (SUM(BYTES)/(1024 * 1024),2) FREE_SPACE
　　FROM DBA_FREE_SPACE
　　GROUPBYTABLESPACE_NAME) F
　　WHERED.TABLESPACE_NAME =F.TABLESPACE_NAME(+)
　　ORDER BY"USED_RATE(%)"DESC;
　　
二．测试
　　
　　我们这里创建2个表空间永久的表空间:
　　1) TS1: 自增长
　　2) TS2：不增长
　　
　　然后分别使用使用上面说的2种方法查询表空间的使用情况。
　　
2.1 创建表空间
　　
　　SQL> create tablespace TS1 datafile'/u01/app/oracle/oradata/dave/ts01.dbf' size 100M autoextend on next 10Mmaxsize 1G;
　　Tablespace created.
　　
　　SQL> create tablespace TS2 datafile'/u01/app/oracle/oradata/dave/ts02.dbf' size 100M autoextend off;
　　Tablespace created.
　　
2.2 使用2种方法查看表空间情况
　　
　　SQL> col tablespace_name for a15
　　SQL> select * from DBA_TABLESPACE_USAGE_METRICSwhere tablespace_name in ('TS1','TS2');
　　
　　TABLESPACE_NAME USED_SPACE TABLESPACE_SIZEUSED_PERCENT
　　--------------- ---------- ---------------------------
　　TS1 128 131072 .09765625
　　TS2 128 12800 1
　　
　　
　　SQL>SELECT D.TABLESPACE_NAME,
　　2 SPACE"SUM_SPACE(M)",
　　3 BLOCKS SUM_BLOCKS,
　　4 SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
　　5 ROUND ( (1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
　　6 FREE_SPACE"FREE_SPACE(M)"
　　7 FROM ( SELECT TABLESPACE_NAME,
　　8 ROUND (SUM(BYTES) / (1024 * 1024), 2) SPACE,
　　9 SUM (BLOCKS) BLOCKS
　　10 FROM DBA_DATA_FILES
　　11GROUP BY TABLESPACE_NAME) D,
　　12( SELECT TABLESPACE_NAME,
　　13 ROUND (SUM (BYTES) / (1024* 1024), 2) FREE_SPACE
　　14 FROM DBA_FREE_SPACE
　　15GROUP BY TABLESPACE_NAME) F
　　16WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME in('TS1','TS2');
　　
　　TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKSUSED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
　　--------------- ------------ ----------------------- ------------ -------------
　　TS2 100 12800 1 1 99
　　TS1 100 12800 1 1 99
　　
　　
2.3 分别在2个表空间创建一样的表
　　
　　SQL> create table D1 tablespace TS1 asselect * from dba_objects;
　　Table created.
　　
　　SQL> insert into D1 select * fromdba_objects;
　　75489 rows created.
　　
　　SQL> insert into D1 select * fromdba_objects;
　　75489 rows created.
　　
　　SQL> insert into D1 select * fromdba_objects;
　　75489 rows created.
　　
　　SQL> insert into D1 select * fromdba_objects;
　　75489 rows created.
　　
　　SQL> commit;
　　Commit complete.
　　
　　
　　SQL> create table D2 tablespace TS2 asselect * from dba_objects;
　　Table created.
　　
　　SQL> insert into D2 select * fromdba_objects;
　　75490 rows created.
　　
　　SQL> insert into D2 select * fromdba_objects;
　　75490 rows created.
　　
　　SQL> insert into D2 select * fromdba_objects;
　　75490 rows created.
　　
　　SQL> insert into D2 select * fromdba_objects;
　　75490 rows created.
　　
　　SQL> commit;
　　Commit complete.
　　
　　
2.4 再次查看表空间变化
　　
　　SQL>select * from DBA_TABLESPACE_USAGE_METRICS where tablespace_name in('TS1','TS2');
　　
　　TABLESPACE_NAME USED_SPACE TABLESPACE_SIZEUSED_PERCENT
　　--------------- ---------- ---------------------------
　　TS1 5632 131072 4.296875
　　TS2 5632 12800 44
　　
　　注意：
　　我们TS1是最大表空间是131072。 因为我们创建TS1表空间时指定的大小是1G。所以我们计算一下：131072*8K/1024= 1024M =1G。这样就正好和我们第一节的理论知识保持一致。
　　通过对比我们也可以确定，我们TS1表空间实际使用的空间和TS2表空间一致，都是5632个block。但是在计算使用率的时候，就出现了偏差。所以对于自动增长的表空间，DBA_TABLESPACE_USAGE_METRICS 视图就不那么适用了。
　　
　　
　　SQL>SELECT D.TABLESPACE_NAME,
　　2 SPACE"SUM_SPACE(M)",
　　3 BLOCKS SUM_BLOCKS,
　　4 SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
　　5 ROUND ( (1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
　　6 FREE_SPACE"FREE_SPACE(M)"
　　7 FROM ( SELECT TABLESPACE_NAME,
　　8 ROUND (SUM(BYTES) / (1024 * 1024), 2) SPACE,
　　9 SUM (BLOCKS) BLOCKS
　　10 FROM DBA_DATA_FILES
　　11GROUP BY TABLESPACE_NAME) D,
　　12( SELECT TABLESPACE_NAME,
　　13 ROUND (SUM (BYTES) / (1024* 1024), 2) FREE_SPACE
　　14 FROM DBA_FREE_SPACE
　　15GROUP BY TABLESPACE_NAME) F
　　16WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME in('TS1','TS2');
　　
　　TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKSUSED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
　　--------------- ------------ ----------------------- ------------ -------------
　　TS2 100 12800 44 44 56
　　TS1 100 12800 44 44 56
　　
　　但我们通过DBA_DATA_FILES和DBA_FREE_SPACE查询结果就是一致的。
　　
　　
三.小结
　　
　　通过上面的对比，dba_tablespace_usage_metrics视图对于非自动增长的表空间，和我们使用传统的DBA_DATA_FILES和DBA_FREE_SPACE查询的结果是一致的。
　　
　　但是对于自动增长的表空间，dba_tablespace_usage_metrics的表空间就不准确了。还是要使用原始的方法来查询。
　　
　　
　　
　　
　　
　　
　　---------------------------------------------------------------------------------------
　　版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
　　Skype: tianlesoftware
　　QQ: tianlesoftware@gmail.com
　　Email: tianlesoftware@gmail.com
　　Blog:  http://blog.csdn.net/tianlesoftware
　　Weibo: http://weibo.com/tianlesoftware
　　Twitter: http://twitter.com/tianlesoftware
　　Facebook: http://www.facebook.com/tianlesoftware
　　Linkedin: http://cn.linkedin.com/in/tianlesoftware