使用SQL脚本查看表空间使用率和使用dba_tablespace_usage_metrics视图的区别

传统的SQL脚本查看表空间使用率,使用的关键视DBA_DATA_FILEDBA_FREE_SPACEOracle 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脚步查询到的结果不一致,而且相差很大。

 

 

 

看一下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

nubmer

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

 

 

 

根据以上分析,可以得出以下结论:

1DBA_TABLESPACE_USED_SPACE是已经分配的空间,对应V$FILESPACE_USAGEALLOCATED_SPACE字段。

2DBA_TABLESPACE_USAGE_METRICSTABLESPACE_SIZE对应V$FILESPACE_USAGEFILE_MAXSIZE字段(而不是FILE_SIZE)。注意:这里对应的是最大值。如果数据文件是自动增长的,那么,对于8kblock,这里的最大值就是32G,也就是通过DBA_TABLESPACE_USAGE_METRICS视图查询显示的4194302blocks

 

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;

 

表已创建。

 

SQL> insert into tt2 select * from dba_objects;

 

已创建11335行。

 

SQL> insert into tt2 select * from dba_objects;

 

已创建11335行。

 

SQL> insert into tt2 select * from dba_objects;

 

已创建11335行。

 

SQL> insert into tt2 select * from dba_objects;

 

已创建11335行。

 

SQL> insert into tt2 select * from dba_objects;

 

已创建11335行。

 

SQL> commit;

 

提交完成。

 

 

4、再用两种方法查看表空间使用情况

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

 

TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT

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

TS1                    896          131072    .68359375

TS2                    896           12800            7

 

通过对比,表空间TS1TS2实际使用的空间是一致的,都是896block。但对于可以扩展的表空间TS1,这里表空间的最大值为131072block,即指定的可以扩展到的1G。由此,计算出的使用率也就出现了偏差。因此,对于自动扩展的表空间,DBA_TABLESPACE_USAGE_METRICS视图就不那么适用了。

 

 

 

 

 

使用脚本:

TABLESPACE_NAME SUM_SPACE SUM_BLOCKS USED_SPACE USED_RATE FREE_SPACE

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

TS1             100M            12800 7.06M      7.06%      92.94M

TS2             100M            12800 7.06M      7.06%      92.94M

 

可以看出,通过脚本查询出的结果,两个表空间的使用率是一致的。

 

 

 

总结:

对于非自动扩展的表空间,使用DBA_TABLESPACE_USAGE_METRICS视图,与传统脚本使用的DBA_DATA_FILEDBA_FREE_SPACE查询的结果是一致的。

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



转自:http://blog.itpub.net/25744374/viewspace-774253

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。