方式1：
sqlplus / as sysdba
col TABLESPACE_NAME for a20
select tbs_used_info.tablespace_name,
tbs_used_info.alloc_mb,
tbs_used_info.used_mb,
tbs_used_info.max_mb,
tbs_used_info.free_of_max_mb,
tbs_used_info.used_of_max || '%' used_of_max_pct
from (select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) alloc_mb,
round((a.bytes_alloc - nvl(b.bytes_free,
0)) / 1024 / 1024) used_mb,
round((a.bytes_alloc - nvl(b.bytes_free,
0)) * 100 / a.maxbytes) used_of_max,
round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,
0)) / 1048576) free_of_max_mb,
round(a.maxbytes / 1048576) max_mb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
order by tbs_used_info.used_of_max desc;
方式2：
set linesize 160 pagesize 1000;
col TABLESPACE_NAME for a20;
col FREE_PCT for a10;
compute sum of Total_MB on report;
compute sum of MAX_MB on report;
break on report;
col FREE_PCT_MAX for a10;
select a.*,b.STATUS,b.CONTENTS
from
(
SELECT total.tablespace_name
,Round(total.MB) AS Total_MB
,Round(free.MB) AS Free_MB
,Round(total.MB-free.MB) AS Used_MB
,Round((free.MB / total.MB) * 100) || '%' AS Free_Pct
,Round(total.MAX_MB) AS MAX_MB
,Round((total.MAX_MB-(total.MB-free.MB))/total.MAX_MB * 100) || '%' AS Free_Pct_Max
FROM (
SELECT tablespace_name
,sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name
) free
,(
SELECT tablespace_name
,sum(CASE AUTOEXTENSIBLE
WHEN 'YES'
THEN MAXBYTES
ELSE BYTES
END) / 1024 / 1024 AS MAX_MB
,sum(BYTES) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name
) total
WHERE free.tablespace_name = total.tablespace_name
union all
SELECT total.tablespace_name
,Round(total.MB) AS Total_MB
,Round(free.MB) AS Free_MB
,Round(total.MB-free.MB) AS Used_MB
,Round((free.MB / total.MB) * 100) || '%' AS Free_Pct
,Round(total.MAX_MB) AS MAX_MB
,Round((total.MAX_MB-(total.MB-free.MB))/total.MAX_MB * 100) || '%' AS Free_Pct_Max
FROM (
SELECT tablespace_name
,sum(free_space) / 1024 / 1024 AS MB
FROM dba_temp_free_space
GROUP BY tablespace_name
) free
,(
SELECT tablespace_name
,sum(CASE AUTOEXTENSIBLE
WHEN 'YES'
THEN MAXBYTES
ELSE BYTES
END) / 1024 / 1024 AS MAX_MB
,sum(BYTES) / 1024 / 1024 AS MB
FROM dba_temp_files
GROUP BY tablespace_name
) total
WHERE free.tablespace_name = total.tablespace_name) a, dba_tablespaces b
where a.tablespace_name=b.tablespace_name;
