Oracle查看表空间利用率脚本

一：查看表空间文件所在位置：

 select file_name , tablespace_name from dba_data_files; 

二：表空间利用率

SELECT D.TABLESPACE_NAME,       SPACE || 'M' "SUM_SPACE(M)",       BLOCKS "SUM_BLOCKS",       SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",       FREE_SPACE || 'M' "FREE_SPACE(M)"  FROM (SELECT TABLESPACE_NAME,               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,               SUM(BLOCKS) BLOCKS          FROM DBA_DATA_FILES         GROUP BY TABLESPACE_NAME) D,       (SELECT TABLESPACE_NAME,               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE          FROM DBA_FREE_SPACE         GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)UNION ALL --如果有临时表空间SELECT D.TABLESPACE_NAME,       SPACE || 'M' "SUM_SPACE(M)",       BLOCKS SUM_BLOCKS,       USED_SPACE || 'M' "USED_SPACE(M)",       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",        NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"  FROM (SELECT TABLESPACE_NAME,               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,               SUM(BLOCKS) BLOCKS          FROM DBA_TEMP_FILES         GROUP BY TABLESPACE_NAME) D,       (SELECT TABLESPACE_NAME,               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE          FROM V$TEMP_SPACE_HEADER         GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1;