Find out Database Growth Month by Month Using V$datafile - Creation_time

advertisements

_____________________________________________________________________________________________________________________

In v$datafile there is a field called creation_time and this field is having the datafile creation date. Suppose if you are database's space addition generally happening by adding the data file then you can easily find out the database growth using this column.

This method will not be effective in the following cases.

a. If you are resizing the existing file this method will not be effective as it records the datafile creation date.

b. If you have done any datafile related reorganisation(recreating the db files) all the creation time will be same date.
select to_char(creation_time, 'YYYY Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v$datafile
where creation_time > SYSDATE-365*2 /*Number of Years: This can be changed according to your requirement. */
group by to_char(creation_time, 'YYYY Month');

Example

select to_char(creation_time, 'YYYY Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v$datafile
where creation_time > SYSDATE-365*2 /* 2 years growth details */
group by to_char(creation_time, 'YYYY Month')
SQL> /

Month          Growth in Meg

-------------- -------------
2013 April             65500
2013 July              32750
2013 December          64750
2013 January           65500
2013 June              65500
2014 February          65500
2014 May               32750
2012 November          65500
2013 March             32750
2013 November          32750
2014 April             65500
2013 August            32750
2013 October           61250
2014 March             32750
2014 June              37000
2013 February          65500
2013 May               65500
2013 September         32750
2014 January           32750
 
19 rows selected.

_____________________________________________________________________________________________________________________

Website Stats