set linesize 400;
set pagesize 200;
col "USED_RATE(%)" format a20;
col "USED_SPACE(M)" format a20;
col "SUM_SPACE(M)" format a20;
col "FREE_SPACE(M)" format a20;
SELECT
D.TABLESPACE_NAME,
SPACE || 'M' AS "SUM_SPACE(M)",
BLOCKS AS "SUM_BLOCKS",
SPACE - NVL(FREE_SPACE, 0) || 'M' AS "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' AS "USED_RATE(%)",
FREE_SPACE || 'M' AS "FREE_SPACE(M)"
FROM (
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) AS SPACE, SUM(BLOCKS) AS BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) D
INNER JOIN (
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) AS FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F ON D.TABLESPACE_NAME = F.TABLESPACE_NAME
UNION ALL
SELECT
D.TABLESPACE_NAME,
SPACE || 'M' AS "SUM_SPACE(M)",
BLOCKS AS SUM_BLOCKS,
USED_SPACE || 'M' AS "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' AS "USED_RATE(%)",
NVL(FREE_SPACE, 0) || 'M' AS "FREE_SPACE(M)"
FROM (
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) AS SPACE, SUM(BLOCKS) AS BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME
) D
INNER JOIN (
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) AS USED_SPACE,
ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) AS FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME
) F ON D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

参考:
http://blog.csdn.net/tianlesoftware/article/details/7619732

ITPub博客

Oracle查看表空间使用率SQL脚本