web analytics

Oracle database growth size

How can we check the size of database growth? I needed to know this while generating a report for management regarding DB size.

Now here a little script that will retrieve the size of database monthly. For execution of this script you will need to have select privilege on sys.v_$datafile view. Following query will retrieve results based on months, however you can easily tweak this query for creation_time column to get any type of extracts.

Script

select to_char(creation_time, 'YYYY Month') "Month",
    sum(bytes)/1024/1024 "Growth in MB"
    from sys.v_$datafile
    where creation_time > SYSDATE-365
    group by to_char(creation_time, 'YYYY Month');

Result

Month          Growth in MB
-------------- ------------
2010 December             2
2010 November            100

One thought on “Oracle database growth size

  • August 9, 2011 at 3:40 am
    Permalink

    I can see that that might well be an appropriate query for an Amazon database – assuming that you use fixed datafile sizes for ease of allocation/automation etc (and if you are that controlled I’d probably be recording growth in a management system elsewhere anyway ). But in general that gives you the “current” size in GB of datafiles added in each of those months. As soon as you allow datafiles to autoextend then the result of that query isn’t really meaningful as a measure of monthly database growth.

Leave a Reply

%d bloggers like this: