2 Replies Latest reply on Jul 14, 2015 9:54 AM by Dom Brooks

    Monthly Database Growth srom specific date onwards


      Hello All,


      Database: Oracle database 11g Enterprise


      Can anyone help me in getting the monthlywise database growth from specific date


      Ex: Start Date: 10-Apr-2015


      Growth(size) until 10-May-2015

      Next Growth(size) 10-Jun-2015

        • 1. Re: Monthly Database Growth srom specific date onwards

          Table space growth:

          select to_char(sp.begin_interval_time,'DD-MM-YYYY') days, ts.tsname,  round(max(tsu.tablespace_size*dt.block_size)/1024/1024, 0) cur_size_MB,  round(max(tsu.tablespace_usedsize*dt.block_size)/1024/1024, 0) used_size_MB,
          round(max(tsu.tablespace_usedsize)/max(tsu.tablespace_size)*100, 1) usage 
          from dba_hist_tbspc_space_usage tsu
             inner join dba_hist_tablespace_stat ts on tsu.tablespace_id=ts.ts#
             inner join dba_hist_snapshot sp on tsu.snap_id=sp.snap_id
             inner join dba_tablespaces dt on ts.tsname=dt.tablespace_name
          where ts.tsname not in ('SYSAUX')
          group by to_char(sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
          order by ts.tsname, days;



          Segments growth:

          column owner format a10

          column object_name format a30

          column "tablespace name" format a15

          select o.owner , o.object_name , o.subobject_name , o.object_type ,
          t.name "tablespace name", s.growth/(1024*1024) "growth in mb",
          (select sum(bytes)/(1024*1024)
             from dba_segments
             where segment_name=o.object_name
          ) "total size(mb)"
          from dba_objects o
             inner join
              (select ts#,obj#, sum(space_used_delta) growth
                from dba_hist_seg_stat
                group by ts#,obj#
                having sum(space_used_delta) > 0
                order by 2 desc
              ) s on s.obj#=o.object_id
             inner join v$tablespace t on s.ts#=t.ts#
          where rownum < 11
          order by 6 desc;

          • 2. Re: Monthly Database Growth srom specific date onwards
            Dom Brooks

            How long is your AWR retention period (DBA_HIST_WR_CONTROL)?

            If it's 7 days then you either need to extend that, or make take regular measurements or maybe OEM has it somewhere.