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
