一．DBA_TABLESPACE_USAGE_METRICS 視圖的理論說明
群裡一朋友說使用dba_tablespace_usage_metrics
dba_tablespace_usage_metrics視圖在oracle 10g中引入，但未公開。 到了Oracle11g公開了這個視圖。因為在Oracle 10g裡可以使用這個視圖，但是在官網文檔裡看不到說明，但是在11g的官方文檔裡可以檢視到該視圖的說明。
Oracle 11g中對這個視圖的說明如下：
DBA_TABLESPACE_USAGE_METRICS describestablespace 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
CORE 11.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
--------------------------------------------------------------------------------
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
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_USAGE summarizes 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(M)"
FROM( SELECTTABLESPACE_NAME,
ROUND (SUM(BYTES)/(1024 * 1024),2) SPACE,
SUM (BLOCKS)BLOCKS
FROM
GROUPBYTABLESPACE_NAME) D,
( SELECTTABLESPACE_NAME,
ROUND (SUM(BYTES)/(1024 * 1024),2)
FROM
GROUPBYTABLESPACE_NAME)
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
11 GROUP BY TABLESPACE_NAME) D,
12 ( SELECT TABLESPACE_NAME,
13 ROUND (SUM (BYTES) / (1024* 1024), 2) FREE_SPACE
14 FROM DBA_FREE_SPACE
15 GROUP BY TABLESPACE_NAME) F
16 WHERE 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
11 GROUP BY TABLESPACE_NAME) D,
12 ( SELECT TABLESPACE_NAME,
13 ROUND (SUM (BYTES) / (1024* 1024), 2) FREE_SPACE
14 FROM DBA_FREE_SPACE
15 GROUP BY TABLESPACE_NAME) F
16 WHERE 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的表空間就不準确了。還是要使用原始的方法來查詢。
![阿裡雲天池龍珠計劃SQL訓練營day1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)