Oracle script to check the database growth

1、Oracle script to check the database growth

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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;

 　　

2、lists the details of database growth per month

 

?

1 2 3 4 5

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