复制代码 代码如下:
/* formatted on 2012/5/31 14:51:13 (qp5 v5.185.11230.41888) */
select d.tablespace_name,
space || ‘m’ “sum_space(m)”,
blocks “sum_blocks”,
space – nvl (free_space, 0) || ‘m’ “used_space(m)”,
round ( (1 – nvl (free_space, 0) / space) * 100, 2) || ‘%’
“used_rate(%)”,
free_space || ‘m’ “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 –如果有临时表空间
select d.tablespace_name,
space || ‘m’ “sum_space(m)”,
blocks sum_blocks,
used_space || ‘m’ “used_space(m)”,
round (nvl (used_space, 0) / space * 100, 2) || ‘%’ “used_rate(%)”,
nvl (free_space, 0) || ‘m’ “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(+)
order by 1;
效果如下：