如何估算oracle 数据库，数据库对象历史增长情况

如何估算oracle 数据库，数据库对象历史增长情况

-----最近七天数据库的增长情况，这个只是一个估算值。

select  sum(space_used_total)/1024/1024/1024 "last 7 days db increase - G"
 from
    dba_hist_seg_stat       s,
    dba_hist_seg_stat_obj   o,
    dba_hist_snapshot       sn
 where
    s.obj# = o.obj#
 and
    sn.snap_id = s.snap_id
 and  begin_interval_time > sysdate-8
 order by
    begin_interval_time
/

以下再补充两个类似的脚本脚本来之网上：

scripts:查看数据库历史增长情况

 
查看数据库历史增长情况
 此处是通过计算数据库所有表空间的历史增长情况来计算数据库历史情况。
--不含undo和temp

with tmp as
(select rtime,
                       sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
                       sum(tablespace_size_kb) tablespace_size_kb
                  from (select rtime,
                               e.tablespace_id,
                               (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
                               (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
                          from dba_hist_tbspc_space_usage e,
                               dba_tablespaces            f,
                               v$tablespace               g
                         where e.tablespace_id = g.TS#
                           and f.tablespace_name = g.NAME
                           and f.contents not in (‘TEMPORARY‘,‘UNDO‘))
                 group by rtime)
       select tmp.rtime,
              tablespace_usedsize_kb,
              tablespace_size_kb,
              (tablespace_usedsize_kb -
              LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
         from tmp,
              (select max(rtime) rtime
                 from tmp
                group by substr(rtime, 1, 10)) t2
        where t2.rtime = tmp.rtime;

--含undo和temp

with tmp as
(select min(rtime) rtime,
                       sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
                       sum(tablespace_size_kb) tablespace_size_kb
                  from (select rtime,
                               e.tablespace_id,
                               (e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
                               (e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
                          from dba_hist_tbspc_space_usage e,
                               dba_tablespaces            f,
                               v$tablespace               g
                         where e.tablespace_id = g.TS#
                           and f.tablespace_name = g.NAME)
                 group by rtime)
       select tmp.rtime,
              tablespace_usedsize_kb,
              tablespace_size_kb,
              (tablespace_usedsize_kb -
              LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
         from tmp,
              (select min(rtime) rtime
                 from tmp
                group by substr(rtime, 1, 10)) t2 where t2.rtime = tmp.rtime

##############################################################
SQL脚本:列出相关段对象在 快照时间内的使用空间的历史变化信息：

column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,‘RRRR-MON-DD‘) start_day,
         sum(a.db_block_changes_delta) block_increase
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in (‘SYS‘,‘SYSTEM‘)
and      end_interval_time between to_timestamp(‘17-FEB-2014‘,‘DD-MON-RRRR‘)
         and to_timestamp(‘25-FEB-2014‘,‘DD-MON-RRRR‘)
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,‘RRRR-MON-DD‘)
order by obj.owner, obj.object_name ;