Home » RDBMS Server » Server Administration » database growth script
database growth script [message #193149] Fri, 15 September 2006 01:32 Go to next message
*Jess*
Messages: 48
Registered: December 2005
Location: Penang, Malaysia
Member

Hi all,

I got this script from Oracle Metalink and tested on our production server.

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


The result shows:
Month Growth in Meg
-------------- -------------
2005 December 2700
2005 November 300
2005 October 3000
2005 September 3500
2006 April 2860
2006 August 10820
2006 January 550
2006 June 3760
2006 March 700

Not sure why the output is not shown for every month, does this relation with the creation_time in the script?
I'm quite fresh in this area and appreciate any guidelines. Thanks.
Re: database growth script [message #193198 is a reply to message #193149] Fri, 15 September 2006 04:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
THis script considers only datafile timestamp ( whenever you create a tablespace or added more datafiles).
If you want a real_growth_rate, you need to maintain a seperate repository kind of information (which is painfull and not much usefull unless you are really into auditing your databases).
You have to find the actual size of every table/index and store those values in a seperate tables and pull reports out of it.
I ***beleive*** in near future Oracle will have this functionality incorporated (already in 10g, stats are collected by default. In that case, the delta between num_rows before and after may give growth rate based on records).
Re: database growth script [message #193491 is a reply to message #193149] Sun, 17 September 2006 19:40 Go to previous messageGo to next message
*Jess*
Messages: 48
Registered: December 2005
Location: Penang, Malaysia
Member

Hi,

Thanks for the info. Does this mean that from my output, in this month, tablespace created on dec 2005 increased 2700Meg, tablespace on Nov 2005 increase 300 and respectively?

The result shows:
Month Growth in Meg
-------------- -------------
2005 December 2700
2005 November 300
2005 October 3000
2005 September 3500
2006 April 2860
2006 August 10820
2006 January 550
2006 June 3760
2006 March 700

Thanks.
Re: database growth script [message #193573 is a reply to message #193491] Mon, 18 September 2006 05:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This output shows no information on which datafile was added/incremented.
All it shows is on Dec 2005 ,a 2700 Meg datafile was added/created,
and on Novemer 2005, a datafile sized 300 M was added.
That is it.
Please be aware that, these filesizes does not reflect the actual database/tablespace usage. These reflect only the allocated space.
Re: database growth script [message #193669 is a reply to message #193149] Mon, 18 September 2006 19:51 Go to previous message
*Jess*
Messages: 48
Registered: December 2005
Location: Penang, Malaysia
Member

Hi,

Thanks for the explanation, have a better picture now.

[Updated on: Mon, 18 September 2006 19:51]

Report message to a moderator

Previous Topic: ORA-12571:TNS Packet writer failure
Next Topic: oracle 9i Heterogeneous Services problem pls help
Goto Forum:
  


Current Time: Wed Dec 07 10:38:47 CST 2016

Total time taken to generate the page: 0.22141 seconds