Oracle表空间的查询脚本

select m.tablespace_name"表空间名",
       m.sum_space||'MB'"总的空间",
       n.free_space||'MB'"空闲空间",
       m.sum_space-nvl(free_space,0)||'MB'"已用空间",
       round((sum_space-nvl(free_space,0))/sum_space*100,3)||'%'"使用率"
from   (select a.tablespace_name,
               round(sum(a.bytes)/(1024*1024),2) sum_space 
               from dba_data_files a
               group by a.tablespace_name)m,
       (select b.tablespace_name,
               round(sum(b.bytes)/(1024*1024),2) free_space
               from dba_free_space b
               group by b.tablespace_name)n
where m.tablespace_name=n.tablespace_name
--加上临时表空间
union all
select o.tablespace_name"表空间名",
       o.temp_sum_space||'MB'"总的空间",
       p.free_space||'MB'"空闲空间",
       nvl(p.used_space,0)||'MB'"已用空间",
       round(nvl(p.used_space,0)/o.temp_sum_space*100,3)||'%'"使用率"
from
       (select c.tablespace_name,
               round(sum(c.bytes)/(1024*1024),2) temp_sum_space
               from dba_temp_files c
               group by c.tablespace_name)o,
       (select d.tablespace_name,
               round(sum(d.bytes_used)/(1024*1024),2) used_space,
               round(sum(d.bytes_free)/(1024*1024),2) free_space
               from v$temp_space_header d
               group by d.tablespace_name)p
               where o.tablespace_name=p.tablespace_name
order by 1;--1就是优先根据select后面查询的第一列排序，2，就根据第二列排序，group by 1类似