一.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
分享到:
相关推荐
Oracle_create_tablespace语法详解.docx
Oracle_tablespace_(表空间)的创建、删除、修改、扩展及检查等
本地管理的oracle 数据文件存储internal
get_tablespace_usage.sh
描述了oracle数据库表空间巡检的步骤: 1:查看表空间 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_...
DBA在日常工作中,最重要的一点就是查看表空间的使用情况,去了解是否有表空间满了的情况出现。 具体方法和步骤如下所示: 第一步:打开PLSQL 第二步:新建一个SQL窗口 第三步:输入代码: select a.tablespace_...
select b.tablespace_name 表空间, c.owner 用户, c.segment_name 表名, b.file_name 物理文件名, sum(nvl(b.bytes, 0)) / 1024 / 1024 总共大小M, round((sum(nvl(b.bytes, 0)) - sum(nvl(a.bytes, 0))) ...
/*第1步:创建临时表空间 */ create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; ...
查看当前用户的缺省表空间 select username,default_tablespace from user_users 查看当前用户的角色 select * from user_role_privs 查看当前用户的系统权限和表级权限 select * from user_sys_...
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 1、查看表空间的名称及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, ...
SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE...
查看表空间大小及已经使用的百分比 复制代码 select a.tablespace_name,a.bytes/1024/1024 "Sum MB", (a.bytes-b.bytes)/1024/1024 "used MB", b.bytes/1024/1024 "free MB", round(((a.bytes-b.bytes)/a....
ERP管理系统资料:SAP专业教材资料T_Tablespace_Ext.doc
oracle tablespace usage
本文详细介绍和阐述了 Oracle 用户(user)和表空间(tablespace)的概念和使用方法等内容,并对用户和表空间使用过程中的注意事项、关键知识点等进行了重点标注和详尽解析,以便于读者进行深入学习和理解。...
You’ll find full coverage of key database issues such as indexing, connectivity, and tablespace usage as they apply to PeopleSoft. Kurtz also provides some of the best advice and information to be ...
select a.TABLESPACE_NAME "TableSpace Name", round(a.BYTES / 1024 / 1024) "MB Allocated", round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) "MB Used", nvl(round(b.BYTES / 1024 / 1024), 0) "MB Free", ...
oracle数据库基本操作 编制时间:2010年2月12日-4月30日 写的很整洁,全是什么操作要用什么命令,多余的话,一句没有 一、表空间操作 1、文件系统: 创建表空间: 数据表空: create tablespace tablespace_name ...
[default tablespace def_tablespace] --指定用户默认表空间 [temporary tablespace temp_tablespace] --指定用户临时表空间 2.用户授权与收回权限 grant DBA to user_name --给用户授予DBA 角色权限 grant create ...
SQL1: 代码如下: –1、查看表空间的名称及大小 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name ...