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

时间：2022-03-14 02:01

传统的SQL脚本查看表空间使用率，使用的关键视DBA_DATA_FILE和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	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

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

1、DBA_TABLESPACE_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;

表已创建。