oracle 表跟踪,Oracle表变化趋势追踪记录

#DBA_HIST_SEG_STAT可以看出对象的使用趋势，构造如下SQL查询出每个时间段内数据库对象的增长量，其中DB_BLOCK_CHANGES_DELTA为块个数

select c.SNAP_ID,

to_char(c.END_INTERVAL_TIME, 'yyyy-mm-dd') SNAP_TIME,

a.OWNER,

a.OBJECT_NAME,

a.OBJECT_TYPE,

b.DB_BLOCK_CHANGES_DELTA

from dba_objects a,

(select SNAP_ID, obj#, DB_BLOCK_CHANGES_DELTA

from DBA_HIST_SEG_STAT

where DB_BLOCK_CHANGES_DELTA > 20000

order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,

DBA_HIST_SNAPSHOT c

where a.object_id = b.obj#

and object_type = 'TABLE'

and b.SNAP_ID = c.SNAP_ID

order by DB_BLOCK_CHANGES_DELTA

#下面的SQL查询出某个时间段内，数据库对象的变化大小，单位以换算成GB

select a.OBJECT_NAME,

to_char(c.END_INTERVAL_TIME, 'yyyy-mm-dd') SNAP_TIME,

sum(b.DB_BLOCK_CHANGES_DELTA) / 1024 / 1024 GB

from dba_objects a,

(select

SNAP_ID, obj#, DB_BLOCK_CHANGES_DELTA

from DBA_HIST_SEG_STAT

where DB_BLOCK_CHANGES_DELTA > 20000

order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,

DBA_HIST_SNAPSHOT c

where a.object_id = b.obj#

and object_type = 'TABLE'

and b.SNAP_ID = c.SNAP_ID

group by a.OBJECT_NAME, c.END_INTERVAL_TIME

order by GB

简单介绍下DBA_HIST_SEG_STAT 。DBA_HIST_SEG_STAT视图内记录了数据库实例启动以来段的变化信息，该视图的数据来自于v$segstat

Column Dataty