8 Replies Latest reply: Aug 3, 2010 9:28 AM by 546494 RSS

    Database Growth %

    Dba_SureshS
      I am using 10.2.0.4

      I have a requirement to calculate space utilized for the one past month(per day). Till now I havent implemented any db growth monitoring scripts.

      Now there is a business requirement to find the volume of transactions & db growth for the past one month.


      Is there anyway to find this Info?


      Thanks,
      Suresh
        • 1. Re: Database Growth %
          CKPT
          Hi,

          this will help you
          http://ayyudba.blogspot.com/2007/09/how-to-check-growth-of-database.html
          • 2. Re: Database Growth %
            Serverprocess
            You can get all the required optimised scripts in the forumn---> http://www.unix.com/


            Thanks,
            Shivaji
            • 3. Re: Database Growth %
              Dba_SureshS
              Thanks for the Info.

              your query would help to find the future growth.

              But I want to the Past growth, how it has grew for everyday (July)..

              Thanks.
              • 4. Re: Database Growth %
                AlokKumar
                It would almost impossible to track space usage in past, unless you have religiously monitoring space growth.

                hare krishna
                • 5. Re: Database Growth %
                  784909
                  U mean u want to find out how the database grown in july ?

                  u cannot find the database growth of july month. For future u can schedule the daily task for all tablespace


                  For freespace track use ( dba_free_space ,dba_data_files) views.


                  Regards
                  Jasbir
                  • 6. Re: Database Growth %
                    Serverprocess
                    Ok I trust it will be a tedieous job to do

                    you have a option called The Oracle LogMiner utility enables you to query redo logs through a SQL interface. Redo logs contain information about the history of activity on a database.

                    http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/logminer.htm#17869

                    Else you can write a script which will give the daily wise status to you with graph.....in future

                    Thanks

                    Shivaji
                    • 7. Re: Database Growth %
                      Lubiez Jean-Valentin
                      Hello,


                      If you are licensed to use AWR (Automatic Workload Repository) which is available in Oracle 10g, you may query the View dba_hist_tbspc_space_usage:
                       
                      select snap_id, tablespace_id, tablespace_usedsize 
                      from dba_hist_tbspc_space_usage 
                      order by tablespace_id, snap_id; 
                      You may get the tablespace_name matching the tablespace_id from the view v$tablespace and the begin time of each snap_id snapshot from the view dba_hist_snapshot.

                      So, by joining these 3 views, you may get the growth history of every Tablespace.


                      Hope this help.
                      Best regards,
                      Jean-Valentin
                      • 8. Re: Database Growth %
                        546494
                        hi,

                        I use the following script to generate database growth
                        Run following create table to create segment info.
                        
                        create table stats$segment_info as
                        select
                          sysdate snap_time,owner, segment_name,segment_type , tablespace_name,sum(bytes) bytes from dba_segments group by owner,segment_type, segment_name,tablespace_name /
                        
                        -- Run following once a calendar month via dba_jobs. 
                        
                        insert into stats$segment_info
                        select
                         sysdate snap_time,owner, segment_name,segment_type , tablespace_name,sum(bytes) bytes from dba_segments group by owner,segment_type, segment_name,tablespace_name / commit;
                        
                        
                        -- run following report to get tablespace growth
                        
                        column this_mon new_value this_mon
                        column last_mon new_value last_mon
                        column prev_mon new_value prev_mon
                        select to_char(sysdate,'MON YYYY') This_mon
                             , To_char(add_months(sysdate,-1),'MON YYYY') Last_mon
                             , to_char(add_months(sysdate,-2),'MON YYYY') prev_mon from dual;
                        
                        column mins format 99999
                        column size_mb heading '&&this_mon' format 99999 column size_mb2 heading '&&prev_mon' format 99999 column size_mb1 heading '&&last_mon' format 99999
                        column growth heading 'Growth(%)|   in   |last month' format 9999.99
                        select tablespace_name,
                        --       to_char(snap_time,'MON YYYY') Month
                               max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-2),'MON'),size_m,0))  size_mb2
                              ,max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-1),'MON'),size_m,0))  size_mb1
                              ,max(decode(trunc(snap_time,'MON'),trunc(sysdate,'MON'),size_m,0))  size_mb
                               ,100*(max(decode(trunc(snap_time,'MON'),trunc(sysdate,'MON'),size_m,0))
                             -    max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-1),'MON'),size_m,0)))
                                         /max(decode(trunc(snap_time,'MON'),trunc(add_months(sysdate,-1),'MON'),size_m,1)) growth from (select  tablespace_name, snap_time
                             ,sum(bytes)/1024/1024 size_M
                        from stats$segment_info
                          where snap_time > trunc(add_months(sysdate,-2),'MON')-2
                        group by tablespace_name,snap_time) a
                        --
                        group by tablespace_name
                        order by tablespace_name
                        /
                        the only way to look at past growth would be to plot growth in the futre from samples and then extrapolate backwards.

                        regards

                        Alan