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级别

发布了149 篇原创文章 · 获赞 92 · 访问量 18万+
展开阅读全文
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览