Script：观察指定表空间的历史增长情况

该脚本用于查看指定表空间的历史增长情况(按小时)（默认是1周）

set lines 200
set pages 200
select u.snap_id,
       to_char(s.end_interval_time, 'yyyy-mm-dd hh24') end_time,
       t.name,
       round(u.tablespace_size * ts.block_size / 1024 / 1024, 2) ts_size_mb,
       round(u.tablespace_usedsize * ts.block_size / 1024 / 1024, 2) ts_used_mb,
       round((u.tablespace_size - u.tablespace_usedsize) * ts.block_size / 1024 / 1024,
             2) ts_free_mb,
       round(u.tablespace_usedsize / u.tablespace_size * 100, 2) pct_used
  from dba_hist_tbspc_space_usage u,
       v$tablespace               t,
       dba_hist_snapshot          s,
       dba_tablespaces            ts
 where u.tablespace_id = t.ts#
   and u.snap_id = s.snap_id
   and t.name = ts.tablespace_name
   and s.instance_number = 1
   and t.name = 'SYSTEM'
   and s.end_interval_time > sysdate - 7
 order by u.snap_id;

按天进行统计：

select a.end_day,
       a.name,
       a.ts_size_mb,
       a.ts_used_mb,
       (a.ts_size_mb - a.ts_used_mb) ts_free_mb,
       round(a.ts_used_mb/a.ts_size_mb*100) pct_used
  from (select to_char(s.end_interval_time, 'yyyy-mm-dd') end_day,
               t.name,
               round(max(u.tablespace_size * ts.block_size) / 1024 / 1024, 2) ts_size_mb,
               round(max(u.tablespace_usedsize * ts.block_size) / 1024 / 1024,
                     2) ts_used_mb
          from dba_hist_tbspc_space_usage u,
               v$tablespace               t,
               dba_hist_snapshot          s,
               dba_tablespaces            ts
         where u.tablespace_id = t.ts#
           and u.snap_id = s.snap_id
           and t.name = ts.tablespace_name
           and s.instance_number = 1
           and t.name = 'SYSTEM'
           and s.end_interval_time > sysdate - 7
         group by to_char(s.end_interval_time, 'yyyy-mm-dd'), t.name) a
 order by 1;

按周进行统计：

select 
   to_char(c.begin_interval_time,'mon') MON,
   to_char(c.begin_interval_time,'ww'),  ---一年中的第几周
   b.name,
   round(max(a.tablespace_size*d.block_size)/1024/1024)     ts_size_mb,
   round(max(a.tablespace_maxsize*d.block_size)/1024/1024)  ts_size_tot,
   round(max(a.tablespace_usedsize*d.block_size)/1024/1024) ts_used
from 
   dba_hist_tbspc_space_usage a, 
   v$tablespace               b,
   dba_hist_snapshot          c, 
   dba_tablespaces            d
where 
   a.tablespace_id=b.ts#
and 
   a.snap_id=c.snap_id
and 
   b.name=d.tablespace_name
and 
   b.name = upper('users')
group by 
   to_char(c.begin_interval_time,'mon'),
   to_char(c.begin_interval_time,'ww'),
   b.name
order by 3,2,1;