$ORACLE_HOME/bin/sqlplus -s "/as sysdba"<col tablespace_name format a17
col SUM_SPACE(M) format 999999
col SUM_BLOCKS format 99999999
col USED_SPACE(M) format 99999999.99 
col USED_RATE(%) format 99.99
col FREE_SPACE(M) format 999999.99
set linesize 200
spool tablespace_report append
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(+);
spool off;

查询结果
TABLESPACE_NAME   SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
----------------- ------------ ---------- ------------- ------------ -------------
UNDOTBS1                    75       9600         31.50        42.00         43.50
SYSAUX                     250      32000        241.06        96.42          8.94
USERS                        5        640           .44         8.80          4.56
SYSTEM                     480      61440        472.62        98.46          7.38
TEMP                        20       2560         19.00        95.00          1.00