表空间使用率的查询，涉及到的视图有DBA_TABLESPACES,DBA_DATA_FILES,DBA_FREE_SPACE,DBA_TEMP_FILES,V$TEMP_EXTENT_POOL，V$TEMP_SPACE_HEADER 。熟悉这些视图的结构，有助物我们在日常的维护中事半功倍。
1.bytes_cached
使用以下脚本，查询表空间的过程中，将视图 v$temp_extent_pool 的 bytes_cached 字段进行 sum 操作，以计算 已使用的临时表空间。对于视图 V$TEMP_EXTENT_POOL ，官方给出的释义是：显示实例缓存和使用的临时空间的状态。请注意，临时空间缓存的加载是惰性的，实例可以处于休眠状态（这里我的理解是，实例使用完了临时空间，并不会立即清理，你不管它不动）。字段 bytes_cached 则是指：缓存的字节数。
也就是说，以下脚本查询结果中，普通表空间是实际的 Total 、实际的 Free、实际的 Used；临时表空间的实际使用值是缓存的字节数，即它并不是当前正在使用，而是在以往的使用过程中缓存占用到了这么多。
对于以下脚本，表空间是否开启自动增长，查询结果都不受其影响。
SELECT
d.status status
, d.tablespace_name name
, d.contents type
, d.extent_management extent_mgt
, d.segment_space_management segment_mgt
, NVL(a.bytes, 0)/1024/1024 ts_size
, NVL(f.bytes, 0)/1024/1024 free
, NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 used
, round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))
pct_used
FROM
sys.dba_tablespaces d
, ( select tablespace_name, sum(bytes) bytes
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.extent_management like 'LOCAL'
AND
d.contents like 'TEMPORARY'
)
UNION ALL
SELECT
d.status status
, d.tablespace_name name
, d.contents type
, d.extent_management extent_mgt
, d.segment_space_management segment_mgt
, NVL(a.bytes, 0)/1024/1024 ts_size
, NVL(a.bytes - NVL(t.bytes,0), 0)/1024/1024 free
, NVL(t.bytes, 0)/1024/1024 used
, TRUNC(NVL(t.bytes / a.bytes * 100, 0)) pct_used
FROM
sys.dba_tablespaces d
, ( select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name
) a
, ( select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name
) t
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
ORDER BY 3,9 desc;2.autoextensible = 'YES'
在实际的生产环境中，当你的表空间中的数据文件都设置为自动增长时，使用以下查询没有问题。但是，如果在同一个表空间中，有的数据文件是YES，而有的数据文件是NO，则会出现以下的情况。AUTOEXTENSIBLE = 'YES' 会计算最大值32GB，否则 是多大，就计算多大。
根据以下脚本 C 表中的计算方法，当 AUTOEXTENSIBLE = 'YES' 时，计重 MAXBYTES，否则计算 bytes。
也就是说：
假如某个表空间中，有两个数据文件1和2，1是自动增长，2是非自动增长。则 SUM（TOTAL）= MAXBYTES + bytes。但是，实际上，这个表空间并没有这么大。
假如某个表空间中，有两个数据文件1和2，1是自动增长，2是自动增长。则 SUM（TOTAL）= MAXBYTES + MAXBYTES。但是，实际上，这个表空间并没有这么大。
假如某个表空间中，有两个数据文件1和2，1是非自动增长，2是非自动增长。则 SUM（TOTAL）= bytes + bytes。这就是这个表空间的实际大小。
个人认为，应按实际大小计算。
set lines 200 pages 200
col name for a20
SELECT d.tablespace_name as Name,
NVL(c.bytes / 1024 / 1024 / 1024, 0) as Size_G,
NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 / 1024 as Used_G,
NVL(c.bytes / 1024 / 1024 / 1024, 0) -
NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 / 1024 as Free_G,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / c.bytes * 100, 0),
'990.00') || '%' as use_rate
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f,
(select tablespace_name, sum(total_bytes) bytes
from (select file_name,
tablespace_name,
case
when (AUTOEXTENSIBLE = 'YES') then
MAXBYTES
else
bytes
end total_bytes
from dba_data_files)
group by tablespace_name) c
WHERE d.tablespace_name = c.tablespace_name(+)
AND d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
order by use_rate desc;3.bytes_used
官方文档说，V$TEMP_SPACE_HEADER 显示每个本地管理的临时表空间中每个文件的汇总信息，包括当前正在使用的空间大小和空间头中标识的空闲空间大小。
以下脚本中，F表和P表所使用的字段 bytes_used 是使用了多少字节（当前正在使用的）。
当然，在实际工作中，可以利用当前正在使用的和缓存的，来判断表空间的相关问题。
--临时表空间使用率
col TABLESPACE_NAME for a18
select
f.tablespace_name,
sum(f.bytes_free + f.bytes_used)/1024/1024/1024 "total_GB",
sum((f.bytes_free + f.bytes_used) - nvl(p.bytes_used,0))/1024/1024/1024 "Free_GB",
sum(nvl(p.bytes_used,0))/1024/1024/1024 "Used_GB"
from sys.v$temp_space_header f,dba_temp_files d,sys.v$temp_extent_pool p
where f.tablespace_name(+)=d.tablespace_name
and f.file_id(+)=d.file_id
and p.file_id(+)=d.file_id
group by f.tablespace_name;>>>如果文章对你有所帮助，欢迎关注、点赞、收藏、评论，谢谢！<<<
