This discussion is archived
1 2 Previous Next 21 Replies Latest reply: May 10, 2013 4:25 AM by pjhitchman RSS

growth of database month wise??

AnkitAshokAggarwal Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Please when you post Thread always mention your DB version, OS version
  • 4. Re: growth of database month wise??
    marksmithusa Journeyer
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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??
    AnkitAshokAggarwal Explorer
    Currently Being Moderated
    Database is 11g and o/s is AIX
  • 10. Re: growth of database month wise??
    Fran Guru
    Currently Being Moderated
    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??
    AnkitAshokAggarwal Explorer
    Currently Being Moderated
    @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 Guru
    Currently Being Moderated
    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??
    AnkitAshokAggarwal Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points