oracle数据库的数据日增量怎么统计

在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图,该视图记录了快照时间内segment-level段级的历史统计信息:DBA_HIST_SEG_STAT displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.
抓AWR快照. 默认是开启的.然后查
SYS@ora11g> select SNAP_ID,ts.name,round(TABLESPACE_SIZE blocksize/1048576) TABLESPACE_SIZE_mb,round(TABLESPACE_USEDSIZEblocksize/1048576) TABLESPACE_USED_maxSIZE,RTIME from DBA_HIST_TBSPC_SPACE_USAGE tsu,ts$ ts where tsu.TABLESPACE_ID = ts.ts# and ts.name='USERS' order by SNAP_ID,ts.name;

SNAP_ID NAME TABLESPACE_SIZE_MB TABLESPACE_USED_MAXSIZE RTIME
---------- ------------ ------------------ ----------------------- -------------------------

 1 USERS             5             4 12/28/2015 11:15:14
 2 USERS             5             4 12/28/2015 12:00:20
 3 USERS            14            12 12/28/2015 13:00:22
 4 USERS            14            12 12/28/2015 13:00:23
 5 USERS            38            36 12/28/2015 13:01:09
 6 USERS               133               131 12/28/2015 13:01:28
 7 USERS               530               504 12/28/2015 13:07:18
 8 USERS              1018               992 12/28/2015 13:11:46

8 rows selected.

SYS@ora11g>

select ss.SNAP_ID,to_char(ss.BEGIN_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_INTERVAL_TIME,hsso.TABLESPACE_NAME,hsso.object_name,hsso.object_type,round(hss.SPACE_ALLOCATED_TOTAL/1048576) SPACE_TOTAL_size_mb,round(hss.SPACE_ALLOCATED_DELTA/1048576) SPACE_ALLOCATED_DELTA_size_mb
from dba_hist_snapshot ss,DBA_HIST_SEG_STAT_OBJ hsso,DBA_HIST_SEG_STAT hss
where

     ss.SNAP_ID = hss.SNAP_ID
and
     hss.OBJ#=hsso.obj#
and
     hsso.ts#=hss.ts#
and
     hsso.TABLESPACE_NAME='USERS'
and
     hsso.object_name='O1'

order by snap_id
SYS@ora11g> /

SNAP_ID BEGIN_INTERVAL_TIME TABLESPACE_NAME OBJECT_NAME OBJECT_TYPE SPACE_TOTAL_SIZE_MB SPACE_ALLOCATED_DELTA_SIZE_MB
---------- ------------------- --------------- ------------------------------ --------------- ------------------- -----------------------------

 3 2015-12-28 12:00:20 USERS           O1                  TABLE                8                  8
 4 2015-12-28 13:00:22 USERS           O1                  TABLE                8                  0
 5 2015-12-28 13:00:23 USERS           O1                  TABLE                   32                 24
 6 2015-12-28 13:01:09 USERS           O1                  TABLE                  127                 95
 7 2015-12-28 13:01:27 USERS           O1                  TABLE                  500                373
 8 2015-12-28 13:07:18 USERS           O1                  TABLE                  988                488

6 rows selected.

SYS@ora11g>

用快照ID和时间对应.来定位每次抓取时的空间使用量.
可以是表空间级别,也可以是segment级别

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值