capacity plan--Tablespace growth yearly base

 

 

Tablespace growth yearly

SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB

from   v$datafile

group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')

order by   1, 2;

 

 

YEAR MO         GB

---- -- ----------

2004 10         14

2004 11         66

montly growth

select

decode

(

grouping_id (to_char(creation_time, 'YYYY MM'))

, 1

, 'Total'

, to_char(creation_time, 'YYYY MM')

) mnth

, sum(bytes)/1024/1024/1024 growth_gb

from v$datafile

where creation_time > SYSDATE - 365

group by rollup (to_char(creation_time, 'YYYY MM'));

*******************************************************

set linesize 200 pagesize 100 colsep "," echo off feedback off timing off

column tablespace_name format a20

column avg_growth_per_day_gb format 9999.99 heading "AVG GROWTH|PER DAY GB"

column projected_growth_for_3mths_gb format 9999.99 heading "PROJECTED|GROWTH|FOR 3 MONTHS|GB"

column projected_growth_for_6mths_gb format 9999.99 heading "PROJECTED|GROWTH|FOR 6 MONTHS|GB"

column projected_growth_for_1yr_gb format 9999.99 heading "PROJECTED|GROWTH|FOR ONE YEAR|GB"

column msg format a15 heading "ACTION|TO BE TAKEN"

with t1 as (

 select ss.run_time,ts.name,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb,

 round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb

 from

 dba_hist_tbspc_space_usage su,

 (select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot

 group by trunc(BEGIN_INTERVAL_TIME) ) ss,

 v$tablespace ts,

 dba_tablespaces dt

 where su.snap_id = ss.snap_id

 and su.tablespace_id = ts.ts#

 and ts.name NOT LIKE '%TEMP%'

 and ts.name NOT LIKE '%UNDO%'

 and ts.name = dt.tablespace_name order by 2,1),

t2 as (

 select e.run_time,e.name,e.used_size_gb,e.used_size_gb - b.used_size_gb growth

 from t1 e, t1 b

 where e.name = b.name and e.run_time = b.run_time +1),

t3 as (

select --fre.tablespace_name, fre.alloc,fre.used,

tsz.name,

tsz.alloc_size_gb,tsz.used_size_gb,ave.avg_growth_per_day_gb,ave.avg_growth_per_day_gb*90 projected_growth_for_3mths_gb 

from

(select name,max(alloc_size_gb) alloc_size_gb, max(used_size_gb) used_size_gb from t1 group by name) tsz,

(select name,round(avg(growth),2) avg_growth_per_day_gb from t2 group by name) ave 

 where tsz.name = ave.name)

select t4.tablespace_name,t4.alloc alloc_sz_gb,t4.used used_sz_gb,

 --t3.alloc_size_gb,t3.used_size_gb,

 t3.avg_growth_per_day_gb,t3.projected_growth_for_3mths_gb,t4.free free_sz_gb,

 case when t4.free < nvl(projected_growth_for_3mths_gb,0) then 'ADD SPACE' end MSG, 

 projected_growth_for_3mths_gb*2 projected_growth_for_6mths_gb , projected_growth_for_3mths_gb*4 projected_growth_for_1yr_gb

 from t3,

 (select a.tablespace_name,

 round(a.bytes/1024/1024/1024,2) alloc,

 round(b.bytes/1024/1024/1024,2) used,

 round(c.bytes/1024/1024/1024,2) free

from sys.sm$ts_avail a,

 sys.sm$ts_used b,

 sys.sm$ts_free c

where a.tablespace_name = b.tablespace_name(+)

 and a.tablespace_name = c.tablespace_name(+)) t4

where t4.tablespace_name = t3.name(+)

 order by 1;

spool capacity_planning.csv

/

spool off