8 Replies Latest reply: Feb 15, 2013 4:18 AM by Osama_Mustafa RSS

    how to find a tablespace growth on a daily basis

    user13364377
      Hi Team,

      How can we find a tablespace growth on a daily basis for a 11.2.0.2 database?

      OS: AIX 6.1
        • 1. Re: how to find a tablespace growth on a daily basis
          Dropbear67
          DBA_HIST_FILESTATXS has the block size of the data files for each AWR snapshot.
          • 2. Re: how to find a tablespace growth on a daily basis
            sb92075
            user13364377 wrote:
            Hi Team,

            How can we find a tablespace growth on a daily basis for a 11.2.0.2 database?

            OS: AIX 6.1
            If answer is important, then query & save results daily.
            • 3. Re: how to find a tablespace growth on a daily basis
              Hemant K Chitale
              If you have the Diagnostic Pack licence, you could query DBA_HIST_TBSPC_SPACE_USAGE.

              Or query DBA_HIST_SEG_STAT


              Hemant K Chitale
              • 4. Re: how to find a tablespace growth on a daily basis
                Step_Into_Oracle_DBA
                ###########################
                !!!Growth of Tablespace
                ###########################

                Change TBS1 to your Tablespace name.
                SELECT TO_CHAR (sp.begin_interval_time,'YYYY-MM-DD') 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 IN ('TBS1') 
                GROUP BY TO_CHAR (sp.begin_interval_time,'YYYY-MM-DD'), ts.tsname 
                ORDER BY ts.tsname, days;
                Please keep forum clean by Marking your Post as Answered or Helpful if Your question is answered.

                Thanks & Regards,
                SID

                (StepIntoOracleDBA)

                Email : stepintooracledba@gmail.com

                http://stepintooracledba.blogspot.in/

                http://www.stepintooracledba.com/
                • 5. Re: how to find a tablespace growth on a daily basis
                  Billy~Verreynne
                  user13364377 wrote:

                  How can we find a tablespace growth on a daily basis for a 11.2.0.2 database?
                  Is that not a pretty meaningless metric? What business value does it, and what business decisions can be based on it?

                  I'm always hesitant to supply metrics like these without knowing the question the metric is suppose to answer. More often than not, the requesting party wanting this metric lacks the knowledge to correctly interpret the metric.
                  • 6. Re: how to find a tablespace growth on a daily basis
                    991266
                    You can schedule and spool this query output to check your tablespace growth on daily basis...
                    set pages 100

                    select a.tablespace_name,
                    A.Allocated,
                    B.Freespace,round(b.freespace/a.allocated*100) "% Free"
                    from
                    (select tablespace_name ,sum(bytes)/1024/1024 Allocated from dba_data_files group by tablespace_name) A ,
                    (select tablespace_name,sum(bytes)/1024/1024 Freespace
                    from dba_free_space group by tablespace_name) B
                    where a.tablespace_name=b.tablespace_name(+) ORDER BY 4
                    /

                    Regards,
                    Nitesh
                    • 7. Re: how to find a tablespace growth on a daily basis
                      moreajays
                      Hi,

                      Better you deploy growth job to query as below and capture in a file , will help in long run
                      Similarly you can capture segment sizes , will help to know the growth of particular segment in case any issues faced
                      column "Tablespace" format a13
                      column "Used MB"    format 99,999,999
                      column "Free MB"    format 99,999,999
                      column "Total MB"   format 99,999,999
                      select
                         fs.tablespace_name                          "Tablespace",
                         (df.totalspace - fs.freespace)              "Used MB",
                         fs.freespace                                "Free MB",
                         df.totalspace                               "Total MB",
                         round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
                      from
                         (select
                            tablespace_name,
                            round(sum(user_bytes) / 1048576) TotalSpace
                         from
                            dba_data_files
                         group by
                            tablespace_name
                         ) df,
                         (select
                            tablespace_name,
                            round(sum(bytes) / 1048576) FreeSpace
                         from
                            dba_free_space
                         group by
                            tablespace_name
                         ) fs
                      where
                         df.tablespace_name = fs.tablespace_name;
                      Thanks,
                      Ajay More
                      http://www.moreajays.com
                      • 8. Re: how to find a tablespace growth on a daily basis
                        Osama_Mustafa
                        http://bit.ly/12JgpaC