Keeping track of database space growth in production environments is a must-to-do job of every DBA. Maintaining and reporting information of historical database growth is always a task that involves good amount of planning and scheduling various jobs in the database.
Oracle provides time-series segment statistics for tables and index in the 10g Automated workload Repository tables (AWR). These can quickly extract data for Oracle growth reports. In Oracle 10g, total database growth reports are is easy because the dba_hist_seg_stat tables provides the delta value for every table and index. Starting from 10g, Oracle has provided us with a feature to achieve this without much overhead. Most importantly, we can use this with out incurring any extra license cost. This technique is used by many DBA’s but not much documentation is available over the internet for easy use.
This script is helpful in tracking the oracle growth
SQL> select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;
Output :
TABLESPACE_NAME CUR_USED_SIZE_MB AVG_INCREAS_MB
-------------------- ----------------------- ------------------------
ENOM_CM_DATA 1121.31 78.71
ENOM_CM_INDEX 2.38 0
ENOM_EL_CATALOG 4.13 .01
ENOM_EL_DATA 13.63 0
ENOM_EL_INDEX 16.06 .01
ENOM_GS_CATALOG 1 0
ENOM_GS_DATA 1 0
ENOM_LA_DATA 5 0
ENOM_LA_INDEX 3 0
EXAMPLE 78.44 0
SYSAUX 614.75 14.46
SYSTEM 692.88 1.32
UNDOTBS1 48 -.96
USERS 644.38 91.47
14 rows selected.
Enjoy :-)
