Tracking Oracle Database Growth

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.



OR
 --Db Size Change
/* Can be useful for identifing total db growth by analyzing all snapshots one by one , and calculating total size change over given period.*/
declare 
--Cursor Declaretion
CURSOR c1
IS 
select sum(round((tablespace_usedsize*8*1024)/1024/1024,2)) as "used space",snap_id from DBA_HIST_TBSPC_SPACE_USAGE 

where snap_id in 
(select snap_id
from dba_hist_snapshot 
where to_char(BEGIN_INTERVAL_TIME) between '16/10/2011%18%' and '20/10/2011%18')
group by snap_id order by snap_id asc;
--Variable Declaretion
row_counter number;
db_used_size_1 number;
db_used_size_2 number;
snap_time timestamp;
total_change number;


begin
row_counter:=0;
total_change:=0;
db_used_size_2:=0;
db_used_size_1:=0;
FOR emp_rec IN c1
LOOP
row_counter:=row_counter+1;
if mod(row_counter,2)=0 
then
db_used_size_2:=emp_rec."used space";
else
db_used_size_1:=emp_rec."used space";
end if;
if row_counter != 1
then
execute immediate 'select begin_interval_time from dba_hist_snapshot where snap_id ='||emp_rec.snap_id into snap_time; 
if db_used_size_2-db_used_size_1 !=0 and db_used_size_1 !=0 and db_used_size_2!=0 
then
if mod(row_counter,2)!=0 
then
dbms_output.put_line(substr(to_char(snap_time),1,instr(snap_time,',')-1)||' -> '
||to_char(db_used_size_1-db_used_size_2)||'MB'||'--> %'||to_char(round(100*(db_used_size_1-db_used_size_2)/db_used_size_1,2)));
total_change:=total_change+(db_used_size_1-db_used_size_2);
else
dbms_output.put_line(substr(to_char(snap_time),1,instr(snap_time,',')-1)||' -> '||to_char(db_used_size_2-
db_used_size_1)||'MB'|| '--> %'||to_char(round(100*(db_used_size_2-db_used_size_1)/db_used_size_1,2)));
total_change:=total_change+(db_used_size_2-db_used_size_1);
end if;

end if;
end if;
END LOOP;
dbms_output.put_line('Total Change ' || to_char(total_change ||' MB' ));

end;

Output:

-> -.82MB--> %-.01

-> 49.88MB--> %.64

-> -28.88MB--> %-.37

-> -2.87MB--> %-.04

-> -.82MB--> %-.01

-> -.68MB--> %-.01

-> .12MB--> %0

-> .19MB--> %0

-> -1.75MB--> %-.02

-> -8.81MB--> %-.11

-> -1MB--> %-.01

-> -.75MB--> %-.01

-> .31MB--> %0

-> -.74MB--> %-.01

-> .31MB--> %0

-> -.75MB--> %-.01

-> -.56MB--> %-.01

-> 1.25MB--> %.02

-> -8.01MB--> %-.1

Total Change 9.5 MB

Cheers!!!!!!!!!!!!!