Oracle表变化趋势追踪记录(原创)

ibaz15m4ln

　　这几天数据库上的用户表空间疯涨，一天的增长量达到了20~30GB,平时就4~5GB的增长量。问了开发商，他们也说不出个所以然。PS：那些鸟开发商，现在我已经对他们绝望了，什么都不知道。情急之下直接对该表空间进行了扩容，问题总算解决了。事后总想弄个明白，想知道是那张表导致的表空间增长如此之快，以免下次出现同样的问题又是不知所措。
　　google了一番后发现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
ColumnDatatypeNULLDescriptionSNAP_IDNUMBER Unique snapshot IDDBIDNUMBER Database ID for the snapshotINSTANCE_NUMBERNUMBER Instance number for the snapshotTS#NUMBER Tablespace numberOBJ#NUMBER Dictionary object numberDATAOBJ#NUMBER Data object numberLOGICAL_READS_TOTALNUMBER Cumulative value for logical readsLOGICAL_READS_DELTANUMBER Delta value for logical readsBUFFER_BUSY_WAITS_TOTALNUMBER Cumulative value for buffer busy waitsBUFFER_BUSY_WAITS_DELTANUMBER Delta value for buffer busy waitsDB_BLOCK_CHANGES_TOTALNUMBER Cumulative value for db block changesDB_BLOCK_CHANGES_DELTANUMBER Delta value for db block changesPHYSICAL_READS_TOTALNUMBER Cumulative value for physical readsPHYSICAL_READS_DELTANUMBER Delta value for physical readsPHYSICAL_WRITES_TOTALNUMBER Cumulative value for physical writesPHYSICAL_WRITES_DELTANUMBER Delta value for physical writesPHYSICAL_READS_DIRECT_TOTALNUMBER Cumulative value for physical reads directPHYSICAL_READS_DIRECT_DELTANUMBER Delta value for physical reads directPHYSICAL_WRITES_DIRECT_TOTALNUMBER Cumulative value for physical writes directPHYSICAL_WRITES_DIRECT_DELTANUMBER Delta value for physical writes directITL_WAITS_TOTALNUMBER Cumulative value for ITL waitsITL_WAITS_DELTANUMBER Delta value for ITL waitsROW_LOCK_WAITS_TOTALNUMBER Cumulative value for row lock waitsROW_LOCK_WAITS_DELTANUMBER Delta value for row lock waitsGC_CR_BLOCKS_SERVED_TOTALNUMBER Cumulative value for global cache CR blocks servedGC_CR_BLOCKS_SERVED_DELTANUMBER Delta value for global cache CR blocks servedGC_CU_BLOCKS_SERVED_TOTALNUMBER Cumulative value for global cache current blocks servedGC_CU_BLOCKS_SERVED_DELTANUMBER Delta value for global cache current blocks servedGC_BUFFER_BUSY_TOTALNUMBER Cumulative value for global cache buffer busyGC_BUFFER_BUSY_DELTANUMBER Delta value for global cache buffer busyGC_CR_BLOCKS_RECEIVED_TOTALNUMBER Cumulative value for global cache CR blocks receivedGC_CR_BLOCKS_RECEIVED_DELTANUMBER Delta value for global cache CR blocks receivedGC_CU_BLOCKS_RECEIVED_TOTALNUMBER Cumulative value for global cache current blocks receivedGC_CU_BLOCKS_RECEIVED_DELTANUMBER Delta value for global cache current blocks receivedSPACE_USED_TOTALNUMBER Cumulative value for space usedSPACE_USED_DELTANUMBER Delta value for space usedSPACE_ALLOCATED_TOTALNUMBER Cumulative value for space allocatedSPACE_ALLOCATED_DELTANUMBER Delta value for space allocatedTABLE_SCANS_TOTALNUMBER Cumulative value for table scansTABLE_SCANS_DELTANUMBER Delta value for table scans　　顺便说一句DBA_HIST_TBSPC_SPACE_USAGE视图可以监控表空间的使用趋势
　　
　　感谢杨帅军的指点
　　参考至:http://oracle.erkansaka.org/2007/09/how-to-determine-tables-with-highest.html
　　            http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3165.htm
　　本文原创，转载请注明出处、作者
如有错误，欢迎指正
　　邮箱:czmcj@163.com