Oracle RAC环境查看表空间使用情况
查询字段释义：
NEED_ADDFILE,--是否需增加表空间文件
TABLESPACE_NAME,--表空间名称
TABLESPACE_FILE_COUNT, --表空间当前数据文件数量
NOW_FILEENABLE_BLOCKS,--表空间文件当前数据块数
NOW_FILEENABLE_BYTES_GB,--表空间文件当前大小
USED_BLOCKS,--已分配的数据块数
USED_BYTES_GB,--已分配的空间大小
FREE_SPACE,--数据文件当前空间中剩余空间大小
MAXBLOCKS,--最大空间数据块数
MAXBYTES_GB,--最大空间数据大小
TABLESPACE_USE_RATE--已分配空间占用率
select NEED_ADDFILE,--是否需增加表空间文件
TABLESPACE_NAME,--表空间名称
TABLESPACE_FILE_COUNT, --表空间当前数据文件数量
NOW_FILEENABLE_BLOCKS,--表空间文件当前数据块数
NOW_FILEENABLE_BYTES_GB,--表空间文件当前大小
USED_BLOCKS,--已分配的数据块数
USED_BYTES_GB,--已分配的空间大小
FREE_SPACE,--数据文件当前空间中剩余空间大小
MAXBLOCKS,--最大空间数据块数
MAXBYTES_GB,--最大空间数据大小
TABLESPACE_USE_RATE--已分配空间占用率
from (SELECT CASE
WHEN TABLESPACE_USE_RATE >= 96 AND
ABS(MAXBYTES_GB - NOW_FILEENABLE_BYTES_GB) <= 2 AND
FREE_SPACE <= 2 THEN
'YES'
ELSE
'NO'
END AS NEED_ADDFILE, --是否需增加表空间文件
A.*
FROM (SELECT A.TABLESPACE_NAME, --表空间名称
TO_CHAR(COUNT(A.FILE_NAME)) AS TABLESPACE_FILE_COUNT, --表空间当前数据文件数量
TO_CHAR(SUM(A.BLOCKS)) AS NOW_FILEENABLE_BLOCKS, --表空间文件当前数据块数
TO_CHAR(SUM(A.BYTES) / 1024 / 1024 / 1024, 9990.099) AS NOW_FILEENABLE_BYTES_GB, --表空间文件当前大小
TO_CHAR(SUM(DECODE(A.MAXBLOCKS,
0,
A.BLOCKS,
A.MAXBLOCKS)) - MAX(B.FREE_BLOCKS)) AS USED_BLOCKS, --已分配的数据块数
TRIM(TO_CHAR(SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024 -
MAX(B.FREE_SPACE),
99990.099)) AS USED_BYTES_GB, --已分配的空间大小
ROUND(MAX(B.FREE_SPACE), 2) FREE_SPACE, --数据文件当前空间中剩余空间大小
TO_CHAR(SUM(DECODE(A.MAXBLOCKS,
0,
A.BLOCKS,
A.MAXBLOCKS))) AS MAXBLOCKS, --最大空间数据块数
TO_CHAR(SUM(DECODE(A.MAXBYTES, 0, A.BYTES, A.MAXBYTES)) / 1024 / 1024 / 1024,
9990.099) AS MAXBYTES_GB, --最大空间数据大小
TO_NUMBER(((SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024) -
MAX(B.FREE_SPACE)) * 100 /
(SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024)) AS TABLESPACE_USE_RATE --已分配空间占用率
FROM DBA_DATA_FILES A
JOIN (SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 FREE_SPACE,
SUM(BLOCKS) FREE_BLOCKS
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME = B.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME, B.TABLESPACE_NAME
UNION ALL
SELECT A.TABLESPACE_NAME AS "表空间名",
TO_CHAR(COUNT(A.FILE_NAME)) AS "表空间文件数",
TO_CHAR(SUM(A.BLOCKS)) AS "当前可用数据块数",
TO_CHAR(SUM(A.BYTES) / 1024 / 1024 / 1024, 9990.099) AS "当前可用大小(G)",
TO_CHAR(MAX(B.USED_BLOCKS)) AS "已分配数据块数",
TRIM(TO_CHAR(MAX(B.USED_SPACE), 99990.099)) AS "已分配大小(G)",
ROUND(MAX(TEMPFREE.FREE_SPACE) / 1024 / 1024 / 1024, 2) 自由空间,
TO_CHAR(SUM(DECODE(A.MAXBLOCKS,
0,
A.BLOCKS,
A.MAXBLOCKS))) AS "可分配最大数据块数",
TO_CHAR(SUM(DECODE(A.MAXBYTES, 0, A.BYTES, A.MAXBYTES)) / 1024 / 1024 / 1024,
9990.099) AS "可分配的最大空间(G)",
TO_NUMBER(MAX(B.USED_SPACE) * 100 /
(SUM(DECODE(A.MAXBYTES,
0,
A.BYTES,
A.MAXBYTES)) / 1024 / 1024 / 1024)) AS "表空间大小使用率"
FROM DBA_TEMP_FILES A
JOIN (SELECT TABLESPACE_NAME,
SUM(BYTES_CACHED) / 1024 / 1024 / 1024 USED_SPACE,
SUM(BLOCKS_CACHED) USED_BLOCKS
FROM (SELECT DISTINCT * FROM GV$TEMP_EXTENT_POOL)
GROUP BY TABLESPACE_NAME) B
ON A.TABLESPACE_NAME = B.TABLESPACE_NAME
JOIN DBA_TEMP_FREE_SPACE TEMPFREE
ON TEMPFREE.TABLESPACE_NAME = A.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME, B.TABLESPACE_NAME) A)
若为非RAC环境，将SQL中的gv$前缀替换为v$便可。
