Wednesday, August 6, 2008

Table growth per month in oracle 10g

The follwoing query gives the changes of each table per day in kilobytes.


SELECT DO.OBJECT_NAME,TO_CHAR(DHS.BEGIN_INTERVAL_TIME,'MM-YYYY') MONTH,
SUM(DHSS.SPACE_USED_DELTA)/1024 KB FROM
DBA_HIST_SNAPSHOT DHS,
DBA_HIST_SEG_STAT DHSS,
V$SEGMENT_STATISTICS SS,
DBA_OBJECTS DO
WHERE DHS.SNAP_ID = DHSS.SNAP_ID
AND SS.OBJ# = DHSS.OBJ#
AND DO.OBJECT_NAME=SS.OBJECT_NAME
AND SS.OBJECT_TYPE='TABLE'
AND DO.OWNER='PROD'
GROUP BY DO.OBJECT_NAME,TO_CHAR(DHS.BEGIN_INTERVAL_TIME,'MM-YYYY')
ORDER BY DO.OBJECT_NAME,TO_CHAR(DHS.BEGIN_INTERVAL_TIME,'MM-YYYY');

No comments:

search engine

Custom Search