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