fbpx

Check Oracle Database Growth per Month

In this article, we are going to check oracle database growth per month. Here we have to script one is for complete database growth and another one is to check growth as per tablespace level.

Table of Contents

Oracle Database Growth per month script

Using the below query you can find out the per month database growth.

SQL>  select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
          from sys.v_$datafile
          group by to_char(creation_time, 'MM-RRRR')
          order by  to_char(creation_time, 'MM-RRRR');

Output:

Oracle Database Growth

Ttablespace level growth script

Using the below script you can check database growth on behalf of the tablespaces.

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:

Oracle Database Growth

Read to Like: My YouTube

Leave a Comment