https://www.xbdba.com/2018/10/18/correct-query-tablespace-usage/
准确查询表空间使用情况
11g以前，一般查询表空间都是通过DBA_DATA_FILE和DBA_FREE_SPACE两个视图来查询。但从11g开始，如果你习惯看EM的话，会发现表空间使用率跟我们以前的sql查出来的结果有出入。这是因为EM采用了新的视图dba_tablespace_usage_metrics，针对自动扩展的表空间，得出来的结果就会不一样。
dba_tablespace_usage_metrics的used_space是已经分配的空间，对应v$filespace_usage的allocated_space字段，而对于非自动扩展的表空间，使用DBA_TABLESPACE_USAGE_METRICS视图，与传统脚本使用的DBA_DATA_FILE和DBA_FREE_SPACE查询的结果是一致的。
SYS@xb> select FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE,MAXBLOCKS from dba_data_files;
FILE_ID TABLESPACE_NAME AUT MAXBLOCKS
---------- ------------------------------ --- ----------
4 USERS YES 4194302
3 UNDOTBS1 YES 4194302
2 SYSAUX YES 4194302
1 SYSTEM YES 4194302
select TABLESPACE_NAME, sum(blocks)*8192/1024/1024 "SIZE (MB)" from dba_data_files
2 where TABLESPACE_NAME='SYSTEM' group by tablespace_name;
TABLESPACE_NAME SIZE (MB)
------------------------------ ----------
SYSTEM 750
select TABLESPACE_NAME, TABLESPACE_SIZE*8192/1024/1024 "SIZE (MB)" from
DBA_TABLESPACE_USAGE_METRICS
3 where tablespace_name='SYSTEM';
TABLESPACE_NAME SIZE (MB)
------------------------------ ----------
SYSTEM 13633.9375
通过上面的结果可以看到，对于自动扩展的system表空间来说，查出来的结果差距接近13g，这是因为DBA_TABLESPACE_USAGE_METRICS视图中的tablespace_size是dba_data_files的最大的块数。也就是dba_tablespace_usage_metrics的tablespace_size是datafile能增长到的最大值。
先看下DBA_TABLESPACE_USAGE_METRICS视图的定义
SYS@xb> set long 99999
SYS@xb> select dbms_metadata.get_ddl('VIEW','DBA_TABLESPACE_USAGE_METRICS') from dual;
DBMS_METADATA.GET_DDL('VIEW','DBA_TABLESPACE_USAGE_METRICS')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SYS"."DBA_TABLESPACE_USAGE_METRICS" ("TABLESPACE_NAME", "USED_SPACE", "TABLESPACE_SIZE"
, "USED_PERCENT") AS
SELECT t.name,
tstat.kttetsused,
tstat.kttetsmsize,
(tstat.kttetsused / tstat.kttetsmsize) * 100
FROM sys.ts$ t, x$kttets tstat 《== 这里发现一个问题，跟官方文档有点区别，一般表空间的数据来源不是v$filespace_usage 而是x$kttets
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#
这里tablespace_size跟dba_data_files的maxblock 并不一致
SYS@xb> select FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE,MAXBLOCKS,MAXBLOCKS*8192/1024/1024 "size(MB)" from dba_data_files;
FILE_ID TABLESPACE_NAME AUT MAXBLOCKS size(MB)
---------- ------------------------------ --- ---------- ----------
4 USERS YES 4194302 32767.9844
3 UNDOTBS1 YES 4194302 32767.9844
2 SYSAUX YES 4194302 32767.9844
1 SYSTEM YES 4194302 32767.9844
SYS@xb> select tablespace_name,tablespace_size,tablespace_size*8192/1024/1024 "size(MB)" from DBA_TABLESPACE_USAGE_METRICS;
TABLESPACE_NAME TABLESPACE_SIZE size(MB)
------------------------------ --------------- ----------
SYSAUX 1716806 13412.5469
SYSTEM 1743686 13622.5469
TEMP 1651420 12901.7188
UNDOTBS1 1661788 12982.7188
USERS 1648326 12877.5469
差异有点大，暂时不清楚是什么原因。个人认为最大block值应该约为32g比较准确，因为都是small file tablespace
关于临时表空间的使用，也许会碰到v$temp_space_header的temp usage怎么大于v$tempseg_usage(或v$sort_usage)的值呢？
select tablespace_name, sum(bytes_used/1024/1024) "gb_used"
from v$temp_space_header
3 group by tablespace_name;
TABLESPACE_NAME gb_used
------------------------------ ----------
TEMP 29
select tablespace, (sum(blocks)*8192)/1024/1024/1024 "gb_used"
from v$tempseg_usage
3 group by tablespace;
未选定行
视图v$sort_usage或者v$tempseg_usage(和v$sort_segment)给出了sort segment分配的正确信息，我们应该通过使用这三个表来查询当前临时空间的确切使用情况的。
但是，v$temp_space_header则是当临时空间使用最高的时候每个临时文件的多少块数，事实上，它展示了每个临时文件初始块的个数并非实际分配的块。v$sort_usage/v$tempseg_usage确切的反映了初始块中对每个事务分配了多少实际的sort extent。
另外，v$temp_space_header的信息是持久化的，即便重启也不会改变；而V$sort_segment and v$sort_usage不是持久的。
正确查看表空间的语句
SELECT /*+ first_rows */
d.tablespace_name "TS NAME",
NVL(a.bytes / 1024 / 1024, 0) "size MB",
NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used MB",
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
a.autoext "Autoextend",
NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
d.status "STAT",
a.count "# of datafiles",
d.contents "TS type",
d.extent_management "EXT MGMT",
d.segment_space_management "Seg Space MGMT"
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT d.contents = 'UNDO'
AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
AND d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
NVL(t.bytes, 0) / 1024 / 1024,
NVL(t.bytes / a.bytes * 100, 0),
a.autoext,
(NVL(a.bytes, 0) / 1024 / 1024 - NVL(t.bytes, 0) / 1024 / 1024),
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name,
sum((ss.used_blocks * ts.blocksize)) bytes
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
and d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
NVL(u.bytes, 0) / 1024 / 1024,
NVL(u.bytes / a.bytes * 100, 0),
a.autoext,
NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024,
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
COUNT(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM (SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = 'ACTIVE'
group by tablespace_name, status
UNION ALL
SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = 'UNEXPIRED'
group by tablespace_name, status)
group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.contents = 'UNDO'
AND d.tablespace_name LIKE '%%'
ORDER BY 1
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
