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