#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
