For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
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
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
But, I am still not sure if the above query is accurate
Citizen_2 wrote:so mark this thread as ANSWEREDBut, I am still not sure if the above query is accurate
Citizen_2 wrote:
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.