1 2 Previous Next 21 Replies Latest reply: May 10, 2013 6:25 AM by pjhitchman RSS

    growth of database month wise??

    Ankit Ashok Aggarwal
      Please share how to find out growth of database month wise?

      according to me, v$datafile can be use to know growth in database in terms of datafiles added in past months as per creation_time.
      but hows about those existing datafiles whose size has been re size more .

      Whats your inputs on this ??
        • 1. Re: growth of database month wise??
          sb92075
          Ankit Ashok Aggarwal wrote:
          Please share how to find out growth of database month wise?

          according to me, v$datafile can be use to know growth in database in terms of datafiles added in past months as per creation_time.
          but hows about those existing datafiles whose size has been re size more .

          Whats your inputs on this ??
          if these details are important to you, then you need to periodically save current size in some permanent data store to you can retrieve it in the future.
          • 2. Re: growth of database month wise??
            TSharma-Oracle
            This below script will give you the tablespace growth over the time depending upon your awr history. You can increase your awr retention time to 30 and then run this script to check the growth.

            select
            to_char (sp.begin_interval_time,'dd-mm-yyyy') days,
            ts.tsname,
            max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_mb,
            max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_mb
            from
            dba_hist_tbspc_space_usage tsu,
            dba_hist_tablespace_stat ts,
            dba_hist_snapshot sp,
            dba_tablespaces dt
            where
            tsu.tablespace_id= ts.ts#
            and
            tsu.snap_id = sp.snap_id
            and
            ts.tsname = dt.tablespace_name
            and
            ts.tsname not in ('SYSAUX','SYSTEM')
            group by
            to_char (sp.begin_interval_time,'dd-mm-yyyy'), ts.tsname
            order by ts.tsname, days;
            • 3. Re: growth of database month wise??
              Osama_Mustafa
              Please when you post Thread always mention your DB version, OS version
              • 4. Re: growth of database month wise??
                marksmithusa
                Unless you're keeping ridiculous amounts of AWR snapshots, then you have to be proactive and measure growth yourself.

                Use cron or the DB scheduler to execute a query to insert whatever usage information you want to compile.
                • 5. Re: growth of database month wise??
                  Aman....
                  I am not sure but I think EM has something that can be used for it, just a thought on top of the mind though.

                  Aman....
                  • 6. Re: growth of database month wise??
                    TSharma-Oracle
                    Aman Paaji, You are right.. This is you have to do

                    Login and go to Reports-->Storage-->Oracle Database Space Usage OR Oracle Database Tablespace Monthly Space Usage
                    • 7. Re: growth of database month wise??
                      Aman....
                      TSharma wrote:
                      Aman Paaji,
                      Wow Punjabi! Nice to hear that! Mind telling me where are you from Tsharma?

                      PS: Sorry for the off-topic reply.

                      Aman....
                      • 8. Re: growth of database month wise??
                        TSharma-Oracle
                        Mind telling me where are you from Tsharma?
                        Originally from Chandigarh..but I did my very first job in Ludhiana Focal Point. Currently in US for last 7 years. Sorry for the off topic guys.We belong to Amritsar.
                        • 9. Re: growth of database month wise??
                          Ankit Ashok Aggarwal
                          Database is 11g and o/s is AIX
                          • 10. Re: growth of database month wise??
                            Fran
                            i use this script:
                            select tsname,
                            round(tablespace_size*t2.block_size/
                            1024/1024,2) TSize,
                            round(tablespace_usedsize*t2.block_size/1024/1024,2) TUsed,
                            round((tablespace_size-tablespace_usedsize)*t2.block_size/1024/1024,2) TFree,
                            round(val1*t2.block_size/1024/1024,2) "Dif_1h",
                            round(val2*t2.block_size/1024/1024,2) "Dif_1d",
                            round(val3*t2.block_size/1024/1024,2) "Dif_1s",
                            round(val4*t2.block_size/1024/1024,2) "Dif_1m",
                            round((tablespace_usedsize/tablespace_size)*100)||'%' "%Used",
                            round(((tablespace_usedsize+val3)/tablespace_size)*100)||'%' "%Proy_1s",
                            round(((tablespace_usedsize+val4)/tablespace_size)*100)||'%' "%Proy_1m",
                            case when ((((tablespace_usedsize+val3)/tablespace_size)*100 < 80) and
                                      (((tablespace_usedsize+val4)/tablespace_size)*100 < 80)) then 'NORMAL'
                                 when ((((tablespace_usedsize+val3)/tablespace_size)*100 between 80 and 90) 
                                         or
                                      (((tablespace_usedsize+val4)/tablespace_size)*100 between 80 and 90)) 
                                then 'WARNING'
                            else 'CRITICAL' end STATUS
                            from
                            (select distinct tsname,
                            rtime,
                            tablespace_size,
                            tablespace_usedsize,
                            tablespace_usedsize-first_value(tablespace_usedsize) 
                            over (partition by tablespace_id order by rtime rows 1 preceding) val1,
                            tablespace_usedsize-first_value(tablespace_usedsize) 
                            over (partition by tablespace_id order by rtime rows 24 preceding) val2,
                            tablespace_usedsize-first_value(tablespace_usedsize) 
                            over (partition by tablespace_id order by rtime rows 168 preceding) val3,
                            tablespace_usedsize-first_value(tablespace_usedsize) 
                            over (partition by tablespace_id order by rtime rows 720 preceding) val4
                            from (select t1.tablespace_size, t1.snap_id, t1.rtime,t1.tablespace_id, 
                                         t1.tablespace_usedsize-nvl(t3.space,0) tablespace_usedsize
                                 from dba_hist_tbspc_space_usage t1,
                                      dba_hist_tablespace_stat t2,
                                      (select ts_name,sum(space) space 
                                       from recyclebin group by ts_name) t3
                                 where t1.tablespace_id = t2.ts#
                                  and  t1.snap_id = t2.snap_id
                                  and  t2.tsname = t3.ts_name (+)) t1,
                            dba_hist_tablespace_stat t2
                            where t1.tablespace_id = t2.ts#
                            and t1.snap_id = t2.snap_id) t1,
                            dba_tablespaces t2
                            where t1.tsname = t2.tablespace_name
                            and rtime = (select max(rtime) from dba_hist_tbspc_space_usage)
                            and t2.contents = 'PERMANENT'
                            order by "Dif_1h" desc,"Dif_1d" desc,"Dif_1s" desc, "Dif_1m" desc
                            I can't remember where i copied it, but it hasn't been made for me.

                            HTH
                            • 11. Re: growth of database month wise??
                              Ankit Ashok Aggarwal
                              @Fran - Its perfect. For a month/week/day/hour analysis.

                              can you help to convert same query into month wise growth (say for last 5 months).

                              Thanks.
                              • 12. Re: growth of database month wise??
                                Fran
                                val1, val2, ... are in hours, 1 month has 720Hours (30days), so you must change it:
                                select tsname,
                                round(tablespace_size*t2.block_size/
                                1024/1024,2) TSize,
                                round(tablespace_usedsize*t2.block_size/1024/1024,2) TUsed,
                                round((tablespace_size-tablespace_usedsize)*t2.block_size/1024/1024,2) TFree,
                                round(val0*t2.block_size/1024/1024,2) "Dif_1m",
                                round(val1*t2.block_size/1024/1024,2) "Dif_2m",
                                round(val2*t2.block_size/1024/1024,2) "Dif_3m",
                                round(val3*t2.block_size/1024/1024,2) "Dif_4m",
                                round(val4*t2.block_size/1024/1024,2) "Dif_5m",
                                round((tablespace_usedsize/tablespace_size)*100)||'%' "%Used",
                                round(((tablespace_usedsize+val3)/tablespace_size)*100)||'%' "%Proy_1s",
                                round(((tablespace_usedsize+val4)/tablespace_size)*100)||'%' "%Proy_1m",
                                case when ((((tablespace_usedsize+val3)/tablespace_size)*100 < 80) and
                                          (((tablespace_usedsize+val4)/tablespace_size)*100 < 80)) then 'NORMAL'
                                     when ((((tablespace_usedsize+val3)/tablespace_size)*100 between 80 and 90) 
                                             or
                                          (((tablespace_usedsize+val4)/tablespace_size)*100 between 80 and 90)) 
                                    then 'WARNING'
                                else 'CRITICAL' end STATUS
                                from
                                (select distinct tsname,
                                rtime,
                                tablespace_size,
                                tablespace_usedsize,
                                tablespace_usedsize-first_value(tablespace_usedsize) 
                                over (partition by tablespace_id order by rtime rows 720  preceding) val0,
                                tablespace_usedsize-first_value(tablespace_usedsize) 
                                over (partition by tablespace_id order by rtime rows 1440preceding) val1,
                                tablespace_usedsize-first_value(tablespace_usedsize) 
                                over (partition by tablespace_id order by rtime rows 2160 preceding) val2,
                                tablespace_usedsize-first_value(tablespace_usedsize) 
                                over (partition by tablespace_id order by rtime rows 2880 preceding) val3,
                                tablespace_usedsize-first_value(tablespace_usedsize) 
                                over (partition by tablespace_id order by rtime rows 3600 preceding) val4
                                from (select t1.tablespace_size, t1.snap_id, t1.rtime,t1.tablespace_id, 
                                             t1.tablespace_usedsize-nvl(t3.space,0) tablespace_usedsize
                                     from dba_hist_tbspc_space_usage t1,
                                          dba_hist_tablespace_stat t2,
                                          (select ts_name,sum(space) space 
                                           from recyclebin group by ts_name) t3
                                     where t1.tablespace_id = t2.ts#
                                      and  t1.snap_id = t2.snap_id
                                      and  t2.tsname = t3.ts_name (+)) t1,
                                dba_hist_tablespace_stat t2
                                where t1.tablespace_id = t2.ts#
                                and t1.snap_id = t2.snap_id) t1,
                                dba_tablespaces t2
                                where t1.tsname = t2.tablespace_name
                                and rtime = (select max(rtime) from dba_hist_tbspc_space_usage)
                                and t2.contents = 'PERMANENT'
                                order by "Dif_1m" desc,"Dif_2m" desc,"Dif_3m" desc, "Dif_4m" desc, "Dif_5m" desc
                                • 13. Re: growth of database month wise??
                                  Ankit Ashok Aggarwal
                                  I suppose this query will work for one month only as it depends of awr retention and rtime column from dba_hist_tbspc_space_usage table.

                                  in my database it has last one month data only as showing below :



                                  SQL> select max(rtime) from dba_hist_tbspc_space_usage;

                                  MAX(RTIME)
                                  -------------------------
                                  02/25/2013 17:30:17

                                  SQL> select min(rtime) from dba_hist_tbspc_space_usage;

                                  MIN(RTIME)
                                  -------------------------
                                  01/28/2013 00:31:31
                                  • 14. Re: growth of database month wise??
                                    Fran
                                    Yes, but, for example, you can move the result of this query to another table to make it as historical of size of your tablespaces.
                                    1 2 Previous Next