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

68 阅读 0 评论 45 点赞

我是靠谱客的博主饱满洋葱，最近开发中收集的这篇文章主要介绍oracle统计每天数据增量,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