DB Growth report - Oracle

Database growth report - Average per day :

For capacity analysis and abnormalities of data load, it is mandatory to manage continuous review of the database utilization growth.

Monthly Database growth report - Average 

select DBG.DATABASE_NAME, DBG.MONTH,avg(DBG.CUR_SIZE_GB),avg(DBG.USEDSIZE_GB)

from

(

SELECT /*+  PARALLEL 4 */  d.name as DATABASE_NAME,TO_CHAR (sp.begin_interval_time,'mon_yy') as  MONTH

, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024*1024),2) ) cur_size_GB

, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) usedsize_GB

FROM DBA_HIST_TBSPC_SPACE_USAGE tsu

, DBA_HIST_SNAPSHOT sp

, DBA_TABLESPACES dt,

v$database d

WHERE 

tsu.snap_id = sp.snap_id

GROUP BY d.name,TO_CHAR (sp.begin_interval_time,'mon_yy')

) DBG

group by DBG.DATABASE_NAME, DBG.MONTH

order by MONTH

;

Monthly DB growth report in pivot table format:

SELECT * FROM

(

select DBG.DATABASE_NAME, DBG.MONTH,avg(DBG.CUR_SIZE_GB) As AVG_TOTAL_GB,

avg(DBG.USEDSIZE_GB) As AVG_USED_GB

from

(

SELECT /*+  PARALLEL 4 */  d.name as DATABASE_NAME,TO_CHAR (sp.begin_interval_time,'MON_YY') as  MONTH

, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024*1024),2) ) cur_size_GB

, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) usedsize_GB

FROM DBA_HIST_TBSPC_SPACE_USAGE tsu

, DBA_HIST_SNAPSHOT sp

, DBA_TABLESPACES dt,

v$database d

WHERE

tsu.snap_id = sp.snap_id

GROUP BY d.name,TO_CHAR (sp.begin_interval_time,'MON_YY')

) DBG

group by DBG.DATABASE_NAME, DBG.MONTH

order by MONTH

)

PIVOT

(

avg(AVG_USED_GB)

for MONTH in ('JAN_20','FEB_20','MARCH_20','APR_20','MAY_20','JUN_20','JUL_20','AUG_20','SEP_20','OCT_20','NOV_20','DEC_20')

)

;