Oracle Database Growth Report

An useful script to find Oracle Databases Growth Report:

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.

=====================================================

Another Useful one found from Neeraj Blog:

=====================================================

/* Can be useful for identifing total db growth by analyzing all snapshots one by one , and calculating total size change over given period.*/
This will give the growth report for 7 days.
set serveroutput on
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 BEGIN_INTERVAL_TIME>= sysdate-7)
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;