Oracle中查看表空间使用率的SQL脚本分享

/* 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;

效果如下：