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 CASEWHEN TABLESPACE_USE_RATE >= 96 ANDABS(MAXBYTES_GB - NOW_FILEENABLE_BYTES_GB) <= 2 ANDFREE_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 AJOIN (SELECT TABLESPACE_NAME,SUM(BYTES) / 1024 / 1024 / 1024 FREE_SPACE,SUM(BLOCKS) FREE_BLOCKSFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) BON A.TABLESPACE_NAME = B.TABLESPACE_NAMEGROUP BY A.TABLESPACE_NAME, B.TABLESPACE_NAMEUNION ALLSELECT 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 AJOIN (SELECT TABLESPACE_NAME,SUM(BYTES_CACHED) / 1024 / 1024 / 1024 USED_SPACE,SUM(BLOCKS_CACHED) USED_BLOCKSFROM (SELECT DISTINCT * FROM GV$TEMP_EXTENT_POOL)GROUP BY TABLESPACE_NAME) BON A.TABLESPACE_NAME = B.TABLESPACE_NAMEJOIN DBA_TEMP_FREE_SPACE TEMPFREEON TEMPFREE.TABLESPACE_NAME = A.TABLESPACE_NAMEGROUP BY A.TABLESPACE_NAME, B.TABLESPACE_NAME) A)

若为非RAC环境，将SQL中的gv$前缀替换为v$便可。