Printable View
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
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
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
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'?