SELECT D.TABLESPACE_NAME ,
SPACE "SUM_SPACE(M)" ,
BLOCKS SUM_BLOCKS ,
SPACE -NVL(FREE_SPACE,0) "USED_SPACE(M)" ,
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",
FREE_SPACE "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 --if have tempfile

SELECT D.TABLESPACE_NAME ,
SPACE "SUM_SPACE(M)" ,
BLOCKS SUM_BLOCKS ,
USED_SPACE "USED_SPACE(M)" ,
ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "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(+)

Related posts:

1. undo相关的sql
2. 查询一天归档文件的大小
3. 等待事件相关的SQL
4. Oracle9i优化器介绍（下）