How to find database growth on a Monthly wise

Today we are going to have look at the Database growth. With the help of the given query. DBA can find the monthly growth of there database. Sometimes little information is too much useful.

select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB

from   v$datafile

group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')

order by   1, 2;

oracle script to check the database growth :

SET LINESIZE 200

SET PAGESIZE 200

COL "Database Size" FORMAT a13

COL "Used Space" FORMAT a11

COL "Used in %" FORMAT a11

COL "Free in %" FORMAT a11

COL "Database Name" FORMAT a13

COL "Free Space" FORMAT a12

COL "Growth DAY" FORMAT a11

COL "Growth WEEK" FORMAT a12

COL "Growth DAY in %" FORMAT a16

COL "Growth WEEK in %" FORMAT a16

SELECT

(select min(creation_time) from v$datafile) "Create Time",

(select name from v$database) "Database Name",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"

FROM    (SELECT BYTES FROM V$DATAFILE

UNION ALL

SELECT BYTES FROM V$TEMPFILE

UNION ALL

SELECT BYTES FROM V$LOG) USED,

(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

Query to find DB Growth Per Month:

COLUMN month FORMAT a75
COLUMN growth FORMAT 999,999,999,999,999

SELECT
TO_CHAR(creation_time, ‘RRRR-MM’) “Month”,
SUM(bytes/1024/1024) “growth in MB”
FROM sys.v_$datafile
GROUP BY TO_CHAR(creation_time, ‘RRRR-MM’)
ORDER BY TO_CHAR(creation_time, ‘RRRR-MM’);

Query to find DB Growth per tablespace, average growth per day and average week growth:

select min(creation_time) “Create Time”, ts.name, round(sum(df.bytes)/1024/1024) curr_size_mb,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)),1) growth_per_day,round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 7,1) growth_7_days from v$datafile df ,v$tablespace ts where df.ts#=ts.ts# group by df.ts#,ts.name order by df.ts#;