Home » RDBMS Server » Backup & Recovery » Database Growth
Database Growth [message #117645] Thu, 28 April 2005 13:32 Go to next message
dtolbert
Messages: 8
Registered: April 2005
Location: Midwest
Junior Member
Does anyone have any suggestions for me. I need to copy data from RMAN to a table so that I may run reports off of it. Or do you know of a way I can get the Oracle internal tables and pu that data into a table to run reports from it to determine database growth. Capacity Planner is not in our budget do I need some type of homegrown SQL or PL/SQL or procedure. Thanks for your help.
Re: Database Growth [message #117704 is a reply to message #117645] Fri, 29 April 2005 01:32 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI

U can use the following script for checking how much ur DB had grown over a period of time (in months). Change the value of 365.

Based on these observations, u can probably figure out the expected growth.

set pagesize 50000
tti "Database growth per month for last year"

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')
/

tti off


Source: http://www.orafaq.com/scripts/sql/dbgrowth.txt
Re: Database Growth [message #117776 is a reply to message #117704] Fri, 29 April 2005 10:04 Go to previous messageGo to next message
dtolbert
Messages: 8
Registered: April 2005
Location: Midwest
Junior Member
Thank you. Do you know of a way to create and save this information in a table?
Re: Database Growth [message #117787 is a reply to message #117776] Fri, 29 April 2005 10:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Dont know what you are looking into exactlY.

The given script does not provide the actual database growth.
It considers only the datafile creation timestamp.
The actual growth is how space is used inside those datafiles/tablespaces.

anyhow to answer your question,
scott@9i > get 1
 1  create table mytable as
 2  select to_char(creation_time, 'RRRR Month') "Month",
 3  sum(bytes)/1024/1024 "Growth in Meg"
 4  from sys.v_
 5  where creation_time > SYSDATE-365
 6* group by to_char(creation_time, 'RRRR Month')
scott@9i > /

Table created.

scott@9i > select * from mytable;

Month          Growth in Meg
-------------- -------------
2005 February             50
2005 January            2175



Re: Database Growth [message #117794 is a reply to message #117787] Fri, 29 April 2005 11:32 Go to previous messageGo to next message
dtolbert
Messages: 8
Registered: April 2005
Location: Midwest
Junior Member
Thank you. Actually heres my problem. We have 33 applications in house. I need to create a table in a central location so that I can create reports in Brio or what every tool for the Buisness to show how that databases are growing for each app.
I wanted to store the information in a table so that I can makes some visuals from the data I was thinking of maybe using the internal data that from RMAN on backup files just a guess.

Thanks,
Re: Database Growth [message #117805 is a reply to message #117794] Fri, 29 April 2005 13:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>We have 33 applications in house. I need to create a table in a central location

simplest thing would be.
1. ONE cronjob that scans through all databases in a loop
this job appends the output to a textfile.
2. Load this text file into any of your database using sqlldr.

Re: Database Growth [message #117806 is a reply to message #117805] Fri, 29 April 2005 13:06 Go to previous messageGo to next message
dtolbert
Messages: 8
Registered: April 2005
Location: Midwest
Junior Member
Not sure I understand you last reply, this in on windows platform can you kinda walk me through your senario, sorry if this sound ridiculous
Re: Database Growth [message #117807 is a reply to message #117806] Fri, 29 April 2005 13:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1. ONE batch file that scans through all databases in a loop.
this job appends the output of the sql script to a textfile.
2. Load this text file into any of your database using sql*loader (a utility to load the text file into oracle table)
or
use external table (in 9i and above).
3. Schedule this batch file using any windows scheduler.
Re: Database Growth [message #117811 is a reply to message #117807] Fri, 29 April 2005 13:46 Go to previous messageGo to next message
dtolbert
Messages: 8
Registered: April 2005
Location: Midwest
Junior Member
The only problem with this is that they are all on different servers any suggestions
Re: Database Growth [message #117812 is a reply to message #117811] Fri, 29 April 2005 14:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
No problems. same scheme should work ( i do this all the time. different servers,versions,Operating system)

run the sql script from the a client (use TNS entries that can talk to all servers)
append the output to one text file.
load using sqlldr.


Re: Database Growth [message #117814 is a reply to message #117812] Fri, 29 April 2005 14:08 Go to previous messageGo to next message
dtolbert
Messages: 8
Registered: April 2005
Location: Midwest
Junior Member
Thanks for all your help. Could you possibly send me a sample script of what you do. This is new to me. I appreciate your help.
Re: Database Growth [message #117815 is a reply to message #117814] Fri, 29 April 2005 14:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Play around with these.
the topics
Run SQL*Plus script from DOS batch file
Schedule a job to execute at regular intervals

http://orafaq.com/scripts/index.htm#WIN

Re: Database Growth [message #118873 is a reply to message #117704] Mon, 09 May 2005 09:00 Go to previous messageGo to next message
rucknrun
Messages: 26
Registered: April 2005
Location: Edgewood, MD
Junior Member
I don't see how this script works? The v$datafile table is the current size of all the files. It doesn't store the sizes over time. When I run it it only shows me when I created new datafiles over the last year. Am I missing something?
Re: Database Growth [message #118877 is a reply to message #118873] Mon, 09 May 2005 09:24 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
rucknrun wrote:
>>The v$datafile table is the current size of all the files. It doesn't store the sizes over time.
True.It doesnt work.
It is NOT THE ACTUAL size / space utilization of database and nothing is historical.
You have to run the script periodically and append the data to a table to get historical information ( Kind of repository).

I mentioned it before and the OP seems not to be bothered.~
Please post your need.

Previous Topic: IMPORT Data dump
Next Topic: RMAN backup hangs !!!!
Goto Forum:
  


Current Time: Tue Apr 23 08:59:27 CDT 2024