Oracle Database Growth Monitoring

Display full database growth per week (Last Seven 7 Days).

Below query display full database growth of last 7 days. To understand query, below query divided into four layer. Upto date tablespace space used view in first layer(layer 1). Tablespace and day wise size increase in second layer(layer 2).  tablespace wise average size increase for last 7 days exclude today in third layer (layer 3). full database growth in last 7 days in fourth layer (layer 4).     

--Layer 4
SELECT SUM(cur_used_size_mb)cur_used_size_mb,SUM(avg_increas_mb)avg_increas_mb

FROM(

--Layer 3
SELECT b.tsname tablespace_name,  --tablespace wise average size increase for last 7 days exclude today  

         MAX (b.used_size_mb) cur_used_size_mb,

         ROUND (AVG (inc_used_size_mb), 2) avg_increas_mb

    FROM (

     --Layer 2
     SELECT a.days, --Tablespace and day wise tablespace size increase

                 a.tsname,

                 used_size_mb,

                   used_size_mb

                 - LAG (used_size_mb, 1) --LAG function is used to access data from a previous row

                      OVER (PARTITION BY a.tsname ORDER BY a.tsname, a.days)

                    inc_used_size_mb

       FROM (  

       --Layer 1 
      SELECT TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY') days, --Upto date Tablespace used

                           ts.tsname,

                           MAX (

                              ROUND (

                                   (usgtb.tablespace_usedsize * dt.block_size)

                                 / (1024 * 1024),

                                 2))

                              used_size_mb

                      FROM dba_hist_tbspc_space_usage usgtb, --Display historical tablespace usage statistics

                           dba_hist_tablespace_stat ts,  --Display tablespace information from the control file

                           dba_hist_snapshot snp, --Display Information about the snapshots in the Workload Repository

                           dba_tablespaces dt --Describes the tablespaces accessible to the current user

                     WHERE     usgtb.tablespace_id = ts.ts#

                           AND usgtb.snap_id = snp.snap_id

                           AND ts.tsname = dt.tablespace_name

                           AND (snp.begin_interval_time) BETWEEN  (SYSDATE -7) and (SYSDATE - 1) --Last 7 days information from  dba_hist_snapshot table exclude today 

                  GROUP BY TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY'),

                           ts.tsname

                  ORDER BY ts.tsname, days  --End Layer 1

                  ) a --End Layer 2
                  ) b --End Layer 3
                  ) --End Layer 4

Explanation

DBA_HIST_TBSPC_SPACE_USAGE --displays historical tablespace usage statistics.

DBA_HIST_SNAPSHOT --displays information about the snapshots in the Workload Repository

DBA_HIST_TABLESPACE_STAT --display tablespace information from the control file

DBA_TABLESPACES --describes the tablespaces accessible to the current user

 BEGIN_INTERVAL_TIME-- Time at the beginning of the snapshot interval, this column belongs to DBA_HIST_SNAPSHOT  table

SNAP_ID --Unique snapshot ID

TABLESPACE_USEDSIZE --Used size of the tablespace (in database blocks), DBA_HIST_TBSPC_SPACE_USAGE table

 LAG --function is used to access data from a previous row

TSNAME--tablespace name

Display tablespace level database growth per week (Last Seven 7 Days)
SELECT b.tsname tablespace_name,  --tablespace wise average size increase for last 7 days exclude today  

         MAX (b.used_size_mb) cur_used_size_mb,

         ROUND (AVG (inc_used_size_mb), 2) avg_increas_mb

    FROM

     (SELECT a.days, --Tablespace and day wise tablespace size increase

                 a.tsname,

                 used_size_mb,

                   used_size_mb

                 - LAG (used_size_mb, 1) --LAG function is used to access data from a previous row

                      OVER (PARTITION BY a.tsname ORDER BY a.tsname, a.days)

                    inc_used_size_mb

       FROM

       (  SELECT TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY') days, --Upto date Tablespace used

                           ts.tsname,

                           MAX (

                              ROUND (

                                   (usgtb.tablespace_usedsize * dt.block_size)

                                 / (1024 * 1024),

                                 2))

                              used_size_mb

                      FROM dba_hist_tbspc_space_usage usgtb, --Display historical tablespace usage statistics

                           dba_hist_tablespace_stat ts,  --Display tablespace information from the control file

                           dba_hist_snapshot snp, --Display Information about the snapshots in the Workload Repository

                           dba_tablespaces dt --Describes the tablespaces accessible to the current user

                     WHERE     usgtb.tablespace_id = ts.ts#

                           AND usgtb.snap_id = snp.snap_id

                           AND ts.tsname = dt.tablespace_name
                           AND (snp.begin_interval_time) BETWEEN  (SYSDATE -8) and (SYSDATE ) --Last 7 days information from  dba_hist_snapshot table exclude today 

                  GROUP BY TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY'),

                           ts.tsname

HAVING TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY') <> TO_CHAR(TRUNC(SYSDATE),'MM-DD-YYYY')                  
ORDER BY ts.tsname, days

                  ) a) b

GROUP BY b.tsname

ORDER BY b.tsname;