OraFAQ Forum: Server Administration » database growth script

Home » RDBMS Server » Server Administration » database growth script

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

database growth script [message #193149]

Fri, 15 September 2006 01:32

*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.

Report message to a moderator

Re: database growth script [message #193198 is a reply to message #193149]

Fri, 15 September 2006 04:41

Mahesh Rajendran
Messages: 10707
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).

Report message to a moderator

Re: database growth script [message #193491 is a reply to message #193149]

Sun, 17 September 2006 19:40

*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.

Report message to a moderator

Re: database growth script [message #193573 is a reply to message #193491]

Mon, 18 September 2006 05:34

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: database growth script [message #193669 is a reply to message #193149]

Mon, 18 September 2006 19:51

*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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 07:33:29 CDT 2024