oracle估算数据增长,如何估算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 ;