Database Growth pattern
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Database Growth pattern

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    Database Growth pattern

    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
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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

  4. #4
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    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'?
    Last edited by Ablakios; 12-01-2004 at 09:52 AM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width