如何获取表增长历史记录信息? (Doc ID 1395195.1)

How To Get Table Growth History Information? (Doc ID 1395195.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 12.1.0.2 [Release 10.2 to 12.1]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

GOAL

With AWR snapshots set up and running at regular intervals, e.g. every 15 or 30 minutes, how to get (recent) table growth history info?

使用AWR快照设置并定期运行，例如 每15或30分钟，如何获取（最近）表增长历史记录信息？

SOLUTION

To view the object (segment) growth in blocks, you can use the views dba_hist_seg_stat and dba_hist_snapshot like below.

要查看 object (segment) 中 blocks 的增长，可以使用如下所示的视图 dba_hist_seg_stat 和 dba_hist_snapshot 。

column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.SPACE_USED_DELTA) block_increase_bytes
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-JAN-2012','DD-MON-RRRR')
         and to_timestamp('02-FEB-2012','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
/

 

NOTE:
Please adapt the time interval and the rest of the query for your own needs. And block_increase_bytes can be negative number indicating that row data in the table might have been deleted / purged.

请根据您自己的需要调整时间间隔和查询的其余部分。 并且 block_increase_bytes 可以为负数，表示表中的行数据可能已被删除/清除。

Sample output:

OWNER            OBJECT_NAME                          START_DAY   BLOCK_INCREASE
---------------- ------------------------------------ ----------- --------------
TEST             FIR_FASTIGH                          2012-JAN-26            128
TEST             FIR_FASTIGH_FUNC                     2012-JAN-26             64
TEST             FIR_FASTIGH_FUNC1                    2012-JAN-26            112
TEST             FIR_FASTIGH_FUNC2                    2012-JAN-26            112
TEST             FIR_FASTIGH_FUNC4                    2012-JAN-27              0
TEST             XX                                   2012-JAN-30             16
...