data guard oracle_实用脚本--合理估算oracle数据库及数据库对象历史增长情况-CSDN博客

概述

很多时候我们都需要估算oracle数据库及数据库对象历史增长情况，来评估是否需要扩容，扩多少。下面介绍一下怎么通过AWR来查找一段时间内，数据库及数据库段对象(堆表、索引)等的空间增长信息。

DBA_HIST_SEG_STAT

在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.

查看数据库历史增长情况

此处是通过计算数据库所有表空间的历史增长情况来计算数据库历史情况。

1、不含undo和temp

with tmp as(select rtime,sum(tablespace_usedsize_kb) tablespace_usedsize_kb,sum(tablespace_size_kb) tablespace_size_kbfrom (select rtime,e.tablespace_id,(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kbfrom dba_hist_tbspc_space_usage e,dba_tablespaces f,v$tablespace gwhere e.tablespace_id = g.TS#and f.tablespace_name = g.NAMEand 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_KBfrom tmp,(select max(rtime) rtimefrom tmpgroup by substr(rtime, 1, 10)) t2where t2.rtime = tmp.rtime;

2、含undo和temp

with tmp as(select min(rtime) rtime,sum(tablespace_usedsize_kb) tablespace_usedsize_kb,sum(tablespace_size_kb) tablespace_size_kbfrom (select rtime,e.tablespace_id,(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kbfrom dba_hist_tbspc_space_usage e,dba_tablespaces f,v$tablespace gwhere 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_KBfrom tmp,(select min(rtime) rtimefrom tmpgroup by substr(rtime, 1, 10)) t2 where t2.rtime = tmp.rtime

列出相关段对象在快照时间内的使用空间的历史变化信息

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('01-MAY-2019', 'DD-MON-RRRR') and to_timestamp('05-MAY-2019', '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;