Oracle查看数据库表空间使用情况

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$便可。