--表空间使用情况：
SELECT UPPER(F.TABLESPACE_NAME) "tabspname",
D.TOT_GROOTTE_MB "tabspsize(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "use(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "ratio",
F.TOTAL_BYTES "free(M)",
F.MAX_BYTES "maxblok(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;

---查询临时表空间使用情况：
Select round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB" ,
d.file_name "Datafile name",
round(nvl(p.bytes_used, 0)/ 1024 / 1024, 2) "Used MB",
round((f.bytes_free + f.bytes_used) / 1024, 2) "total KB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2) "Free KB",
round(nvl(p.bytes_used, 0)/ 1024, 2) "Used KB",0 "Fragmentation Index"
from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id;