当前位置： 移动技术网 > IT编程>数据库>Oracle > Oracle中查看表空间使用率的SQL脚本分享

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

2017年12月12日  | 移动技术网IT编程  | 我要评论

复制代码 代码如下:

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

效果如下：

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网