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 Datatype  NULL  Description 

SNAP_ID   NUMBER      Unique snapshot ID  
DBID   NUMBER      Database ID for the snapshot  
INSTANCE_NUMBER   NUMBER      Instance number for the snapshot  
TS#   NUMBER      Tablespace number  
OBJ#   NUMBER      Dictionary object number  
DATAOBJ#   NUMBER      Data object number  
LOGICAL_READS_TOTAL   NUMBER      Cumulative value for logical reads  
LOGICAL_READS_DELTA   NUMBER      Delta value for logical reads  
BUFFER_BUSY_WAITS_TOTAL   NUMBER      Cumulative value for buffer busy waits  
BUFFER_BUSY_WAITS_DELTA   NUMBER      Delta value for buffer busy waits  
DB_BLOCK_CHANGES_TOTAL   NUMBER      Cumulative value for db block changes  
DB_BLOCK_CHANGES_DELTA   NUMBER      Delta value for db block changes  
PHYSICAL_READS_TOTAL   NUMBER      Cumulative value for physical reads  
PHYSICAL_READS_DELTA   NUMBER      Delta value for physical reads  
PHYSICAL_WRITES_TOTAL   NUMBER      Cumulative value for physical writes  
PHYSICAL_WRITES_DELTA   NUMBER      Delta value for physical writes  
PHYSICAL_READS_DIRECT_TOTAL   NUMBER      Cumulative value for physical reads direct  
PHYSICAL_READS_DIRECT_DELTA   NUMBER      Delta value for physical reads direct  
PHYSICAL_WRITES_DIRECT_TOTAL   NUMBER      Cumulative value for physical writes direct  
PHYSICAL_WRITES_DIRECT_DELTA   NUMBER      Delta value for physical writes direct  
ITL_WAITS_TOTAL   NUMBER      Cumulative value for ITL waits  
ITL_WAITS_DELTA   NUMBER      Delta value for ITL waits  
ROW_LOCK_WAITS_TOTAL   NUMBER      Cumulative value for row lock waits  
ROW_LOCK_WAITS_DELTA   NUMBER      Delta value for row lock waits  
GC_CR_BLOCKS_SERVED_TOTAL   NUMBER      Cumulative value for global cache CR blocks served  
GC_CR_BLOCKS_SERVED_DELTA   NUMBER      Delta value for global cache CR blocks served  
GC_CU_BLOCKS_SERVED_TOTAL   NUMBER      Cumulative value for global cache current blocks served  
GC_CU_BLOCKS_SERVED_DELTA   NUMBER      Delta value for global cache current blocks served  
GC_BUFFER_BUSY_TOTAL   NUMBER      Cumulative value for global cache buffer busy  
GC_BUFFER_BUSY_DELTA   NUMBER      Delta value for global cache buffer busy  
GC_CR_BLOCKS_RECEIVED_TOTAL   NUMBER      Cumulative value for global cache CR blocks received  
GC_CR_BLOCKS_RECEIVED_DELTA   NUMBER      Delta value for global cache CR blocks received  
GC_CU_BLOCKS_RECEIVED_TOTAL   NUMBER      Cumulative value for global cache current blocks received  
GC_CU_BLOCKS_RECEIVED_DELTA   NUMBER      Delta value for global cache current blocks received  
SPACE_USED_TOTAL   NUMBER      Cumulative value for space used  
SPACE_USED_DELTA   NUMBER      Delta value for space used  
SPACE_ALLOCATED_TOTAL   NUMBER      Cumulative value for space allocated  
SPACE_ALLOCATED_DELTA   NUMBER      Delta value for space allocated  
TABLE_SCANS_TOTAL   NUMBER      Cumulative value for table scans  
TABLE_SCANS_DELTA   NUMBER      Delta value for table scans