使用SQL指令碼檢視錶空間使用率和使用dba_tablespace_usage_metrics檢視的區別

傳統的SQL指令碼檢視錶空間使用率，使用的關鍵檢視是 DBA_DATA_FILES和DBA_FREE_SPACE。Oracle 11g引入了 DBA_TABLESPACE_USAGE_METRICS檢視。事實上，Oracle 10g中就已經引入了該檢視，可以使用，但在10g官方文件中查不到，11g官方文件對該檢視作了說明。

Oracle 11g 官方文件中對DBA_TABLESPACE_USAGE_METRICS檢視的說明：

DBA_TABLESPACE_USAGE_METRICS describes tablespace 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,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腳步查詢到的結果不一致，而且相差很大。

傳統的SQL腳步檢視錶空間使用率的方法已經總結過：

http://space.itpub.net/25744374/viewspace-774219

看一下DBA_TABLESPACE_USAGE_METRICS檢視的定義語句：

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

t.ts# = f.tablespace_id

GROUP BY t.name, f.tablespace_id

-----------------------------------------------------------------

可以看出，DBA_TABLESPACE_USAGE_METRICS查詢到的關鍵資料出自v$filespace_usage檢視。

對 v$filespace_usage檢視，11g官方文件的說明如下：

V$FILESPACE_USAGE summarizes space allocation 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欄位（而不是FILE_SIZE）。注意：這裡對應的是最大值。如果資料檔案是自動增長的，那麼，對於8k的block，這裡的最大值就是32G，也就是通過DBA_TABLESPACE_USAGE_METRICS檢視查詢顯示的4194302個blocks。

SQL> select * from dba_tablespace_usage_metrics;

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT

------------------------------ ---------- --------------- -------

SYSAUX 16576 4194302 .395202825

SYSTEM 33912 4194302 .808525471

TEMP 0 4194302 0

UNDOTBS1 288 4194302 .006866458

USERS 24 4194302 .000572205

而下面是用SQL指令碼查詢到的表空間使用率情況，出入很大：

TABLESPACE_NAME SUM_SPACE SUM_BLOCKS USED_SPACE USED_RATE FREE_SPACE

---------- ---------- ---------- ---------- ---------- ----------

SYSAUX 140M 17920 129.56M 92.54% 10.44M

SYSTEM 300M 38400 265M 88.33% 35M

TEMP 20M 2560 16M 80% 4M

UNDOTBS1 200M 25600 19.31M 9.66% 180.69M

USERS 5M 640 .19M 3.8% 4.81M

下面測試對於自動擴充套件和非自動擴充套件的表空間，兩種方法的查詢結果：

1、建立表空間

TS1：自動擴充套件；

TS2：非自動擴充套件。

SQL> create tablespace TS1 datafile 'E:\oracle\product\10.2.0\oradata\ORCL\DATAFILE\ts01.dbf' size 100M autoextend on next 10M maxsize 1G;

表空間已建立。

SQL> create tablespace TS2 datafile 'E:\oracle\product\10.2.0\oradata\ORCL\DATAFILE\ts02.dbf' size 100M autoextend off;

表空間已建立。

2、分別使用兩種方法檢視錶空間情況

（1）、使用DBA_TABLESPACE_USAGE_METRICS檢視

SQL> select * from dba_tablespace_usage_metrics where tablespace_name in ('TS1','TS2');

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT

------------------------------ ---------- --------------- -------

TS1 0 131072 0

TS2 0 12800 0

（2）、使用SQL指令碼檢視

TABLESPACE_NAME SUM_SPACE SUM_BLOCKS USED_SPACE USED_RATE FREE_SPACE

--------------- ---------- ---------- ---------- ----------

TS1 100M 12800 .06M .06% 99.94M

TS2 100M 12800 .06M .06% 99.94M

3、分別在兩個表空間建立一樣的表，插入資料

SQL> create table tt1 tablespace ts1 as select * from dba_objects;

表已建立。

SQL> insert into tt1 select * from dba_objects;

已建立11334行。

SQL> insert into tt1 select * from dba_objects;

已建立11334行。

SQL> insert into tt1 select * from dba_objects;

已建立11334行。

SQL> insert into tt1 select * from dba_objects;

已建立11334行。

SQL> insert into tt1 select * from dba_objects;

已建立11334行。

SQL> commit;

提交完成。

SQL> create table tt2 tablespace ts2 as select * from dba_objects;

表已建立。