1. 表空间使用率查询脚本

1.脚本说明

*脚本包含Temp、Undo表空间
*因往常脚本忽略数据文件的自动扩展特性，导致表空间使用率查询结果不准确
*本脚本是基于磁盘或者ASM磁盘充足的情况，表空间最大容量需要建立在物理空间充足的情况

2.脚本如下
SELECT TABLESPACE_NAME,
MAX_GB,
USED_GB,
MAX_GB - USED_GB FREE_GB,
ROUND(100 * USED_GB / MAX_GB,2) PCT_USED
FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / POWER(2, 30),
2) USED_GB,
ROUND(A.MAXBYTES / POWER(2, 30), 2) MAX_GB
FROM (SELECT F.TABLESPACE_NAME,
SUM(F.BYTES) BYTES_ALLOC,
SUM(DECODE(F.AUTOEXTENSIBLE,
‘YES’,
F.MAXBYTES,
‘NO’,
F.BYTES)) MAXBYTES
FROM DBA_DATA_FILES F
GROUP BY TABLESPACE_NAME) A,
(SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_FREE
FROM DBA_FREE_SPACE F
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
UNION ALL
SELECT H.TABLESPACE_NAME TABLESPACE_NAME,
ROUND(SUM(NVL(P.BYTES_USED, 0)) / POWER(2, 30), 2) USED_GB,
ROUND(SUM(DECODE(F.AUTOEXTENSIBLE,
‘YES’,
F.MAXBYTES,
‘NO’,
F.BYTES)) / POWER(2, 30),
2) MAX_GB
FROM V$TEM{P}_{S}PAC{E}_{H}EADERH,V$TEMP_EXTENT_POOL P, DBA_TEMP_FILES F
WHERE P.FILE_ID(+) = H.FILE_ID
AND P.TABLESPACE_NAME(+) = H.TABLESPACE_NAME
AND F.FILE_ID = H.FILE_ID
AND F.TABLESPACE_NAME = H.TABLESPACE_NAME
GROUP BY H.TABLESPACE_NAME)
ORDER BY 5 asc;