Database Growth pattern

[Ablakios]

Hi All
The following script gives me the database growth as follows

select to_char(creation_time, 'RRRR Month') "MONTH",
sum(bytes)/1024/1024/1024 "GROWTH IN GIG"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');

MONTH GROWTH IN GB
2004 JAN 8
2004 AUG 6
2004 OCT 5


How can I manipute the above query to give the growth patern as follows
2004 JAN
2004 FEB
2004 MAR
--
--
--
2004 NOV

Any help will be highly appreciated.
Thanks

[tamilselvan]

First of all your query does not give growth pattern. What you got is total allocated space.

You need to create a table in which you store the used space every month.

Tamil

[gopi]

I do what Tamil advises. I put the following in cron'ed script and run weekly. If ur not on UNIX then schedule it another way (DBMS_JOB).

$ORACLE_HOME/bin/sqlplus -s << EOF > /tmp/growth_rate.log
connect / as sysdba
set head off
set serveroutput on
insert into growth_rate
select sysdate, tablespace_name, sum(bytes), round(((sum(bytes) /1024) /1024))
from dba_segments
where tablespace_name not in ('TOOLS','UNDOTBS','USER_DATA')
group by sysdate, tablespace_name;
commit;
EOF

then all ya gotta do is query growth_rate (aggregate by month and subtract from previous month to get growth).
HTH

[Ablakios]

Tamil and Gopy
Thanks for the prompt response
Could please elaborate more on the process?
Gopy, your script stated
(where tablespace_name not in ('TOOLS','UNDOTBS','USER_DATA')
I can understand tablespace_name not in('TOOLS','UNDOTBS') but USER_DATA is if I am not mistaken a part of the database that grows considerably. Why not in 'USER_DATA'?