DevOps, CI/CD and Automation

Hello, that query will tell you the growth of the database only where new datafiles have been added, ie, it will take no account of existing datafiles that have been increased in size.

This query would allow you to order by time:

select to_char(creation_time, 'YYYYMM') "Month",

   sum(bytes)/1024/1024/1024 "Growth in GBs"

  from v$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'YYYYMM')

order by 1;

Other than that, you'd need Oracle Enterprise Manager (OEM) Grid Control, which has database growth as a reporting tab (to the best of my knowledge). 

the query from the MOS document tells you the size of the files which have been created in the last year: it does not contain files older than 365 days and does not monitor the file growth.

In my opinion the most solid approach is to create a table and a job to add the rows from v$datafile and a corresponding timestamp. Not built-in but it works also in standard edition systems.

Please realize & understand that default behavior for Oracle database is that for all data files AUTOEXTENSIBLE=NO

This means that by definition thee will be NO database growth.

I found the following query. It uses DBA_FREE_SPACE. Output is just one line. It just shows one figure for daily growth which I can convert to monthly.

Can I use this query's output to forecast the DB growth with acceptable level of accuracy ? . Screenshot of the output from my DB shown below

http://amitpawardba.blogspot.my/2015/10/oracle-script-to-check-database-growth.html

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 a14

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;

7670 MB per Day means 210GB per month

so mark this thread as ANSWERED

so mark this thread as ANSWERED

But, I am still not sure if the above query is accurate

Citizen_2 wrote:
so mark this thread as ANSWERED
But, I am still not sure if the above query is accurate

Oracle is too dumb to lie about the results.

the query from the blog has the same limitation as the inital query from the MOS document: it uses only v$datafile and the creation dates of datafiles to determine the database growth. So it is only useful if your datafiles are fixed sized and no file extension takes place (either automatic or manual).

Citizen_2, if is not difficult to look at the data and determine if the query itself produces correct results.  The real question should be if the query meets your requirements.  If you want results at the file level it does not, but if an overall value for the database then it may well.  You have to consider if you want to include things like the online REDO and FRA in your numbers or not and make a few adjustments to the query based on your needs.

- -

Personally I think Martin gave you the best answer if you want to track this at the tablespace or file level.  Create a job that snapshots DBA_DATA_FILES or V$DATAFILE and set it up to run at the desired tracking interval.  Then you would have the ability to go back and produce a report of the change for any desired interval such as past three months, last year, life of database, or anything in between.

- -

And yes I agree with John that you should mark the thread as answered as you have been provided with your options.  It is now up to you to make some decisions and perhaps write some SQL and/or PL/SQL to get exactly what you want.

- -

HTH -- Mark D Powell --

create table db_size ( dte date, sz int );

Table created.

SQL>
SQL> insert into db_size
  2  select sysdate, sum(bytes/1024/1024)
  3  from dba_segments;

Set up a job to do the insert daily to capture the info. Than write a query for the date ranges you want

As long as you are tracking things, you might keep a record of backup size.  If you do exports, that could be another measure.

You might also want to know transaction volume, one way to track that is size of archived logs.

Sometimes the OS is best for tracking overall capacity.  When things get tight, you have to make managerial decisions about things like how much backup to keep and perhaps review SLA's.