表空间、数据文件增长情况

 
 
--表空间使用量

select t.*
  from (select d.tablespace_name "tablespace_name",
               space "sum_space(G)",
               space - nvl(free_space, 0) "used_space(G)",
               round((1 - nvl(free_space, 0) / space) * 100, 2) "used_rate(%)",
               free_space "free_space(M)"
          from (select tablespace_name,
                       round(sum(bytes) / (1024 * 1024 * 1024), 3) space
                  from dba_data_files
                 group by tablespace_name) d,
               (select tablespace_name,
                       round(sum(bytes) / (1024 * 1024 * 1024), 3) 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 "sum_space(m)",
               used_space "used_space(m)",
               round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)",
               space - used_space "free_space(m)"
          from (select tablespace_name,
                       round(sum(bytes) / (1024 * 1024 * 1024), 3) space
                  from dba_temp_files
                 group by tablespace_name) d,
               (select tablespace,
                       round(sum(blocks * 8192) / (1024 * 1024 * 1024), 3) used_space
                  from v$sort_usage
                 group by tablespace) f
         where d.tablespace_name = f.tablespace(+)) t
 order by "used_rate(%)" desc;
 
 
--单个表空间的增长情况,以 小时 为 粒度
select t3.tablespace_id "tablespace_id",
       t3.name  "tablespace_name",
       t3.full_size "full_size(M)",
       t3.used_size "used_size(M)",
       t3.used_size / t3.full_size * 100 "used_ratio(%)",
       to_char(to_date(t3.db_time, 'mm-dd-yyyy hh24:mi:ss'),
               'yyyy-mm-dd hh24:mi:ss') "time"
  from (select distinct t1.tablespace_id,
                        t2.name,
                        t1.tablespace_size * 8192 / 1024 / 1024 full_size,
                        t1.tablespace_usedsize * 8192 / 1024 / 1024 used_size,
                        t1.rtime db_time
          from dba_hist_tbspc_space_usage t1, v$tablespace t2
         where t1.tablespace_id = t2.ts#
         and t2.name=upper('&tbs_name')
         order by t1.rtime desc) t3;

--使用绑定变量

   --声明绑定变量
var tbs_name varchar2(20);

   --绑定变量赋值
exec :tbs_name :='users';

   --运用绑定变量
select t3.tablespace_id "tablespace_id",
           t3.name  "tablespace_name",
           t3.full_size "full_size(M)",
           t3.used_size "used_size(M)",
           t3.used_size / t3.full_size * 100 "used_ratio(%)",
           to_char(to_date(t3.db_time, 'mm-dd-yyyy hh24:mi:ss'),
                   'yyyy-mm-dd hh24:mi:ss') "time"
      from (select distinct t1.tablespace_id,
                            t2.name,
                           t1.tablespace_size * 8192 / 1024 / 1024 full_size,
                           t1.tablespace_usedsize * 8192 / 1024 / 1024 used_size,
                           t1.rtime db_time
             from dba_hist_tbspc_space_usage t1, v$tablespace t2
            where t1.tablespace_id = t2.ts#
            and t2.name=upper(:tbs_name)
   order by t1.rtime desc) t3;

          
--数据文件的增长情况