oracle 查询表空间大小

oracle 查询表空间大小

--查询表空间
SELECT D.TABLESPACE_NAME "表空间名",  
       SPACE || 'TB' "总大小(TB)",  
       SPACE - NVL (FREE_SPACE, 0) || 'TB' "已使用大小(TB)",  
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 6) || '%'  
          "使用率(%)",  
       FREE_SPACE || 'TB' "剩余大小(TB)"  
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024 * 1024 * 1024), 6) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_DATA_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024 * 1024 * 1024), 6) FREE_SPACE  
            FROM DBA_FREE_SPACE  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

--查询临时表空间
SELECT D.TABLESPACE_NAME "表空间名",  
       SPACE || 'TB' "总容量(TB)",  
       USED_SPACE || 'TB' "已使用大小(TB)",  
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 6) || '%' "使用率(%)",  
       NVL (FREE_SPACE, 0) || 'TB' "剩余大小(TB)"  
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024 * 1024 * 1024), 6) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_TEMP_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES_USED) / (1024 * 1024 * 1024 * 1024), 6) USED_SPACE,  
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024 * 1024 * 1024), 6) FREE_SPACE  
            FROM V$TEMP_SPACE_HEADER  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)