Description :
This script is very useful for calculate and report to database growth in day/week/month/year wise.
Script:
SELECT
(select min(creation_time) from gv$datafile) “Create Time”,
(select name from gv$database) “Database Name”,
ROUND((SUM(USED.BYTES) / 1024 / 1024 /1024 ),2) || ‘ GB’ “Database Size”,
ROUND((SUM(USED.BYTES) / 1024 / 1024 /1024 ) – ROUND(FREE.P / 1024 / 1024 /1024 ),2) || ‘ GB’ “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 /1024 ),2) || ‘ GB’ “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 gv$datafile),2) || ‘ MB’ “Growth DAY”,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) – (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from gv$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 gv$datafile)*7,2) || ‘ MB’ “Growth WEEK”,
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) – (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from gv$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || ‘% MB’ “Growth WEEK in %”
FROM (SELECT BYTES FROM gV$DATAFILE
UNION ALL
SELECT BYTES FROM gV$TEMPFILE
UNION ALL
SELECT BYTES FROM gV$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
