Tablespace Capacity Planning Report

I need to run capacity planning report in my project.

Requirements:

1. Predict db growth for 3 months, 6 months and one year.
2. Display message, if the available free space in each tablespace is less than it's projected 3 months growth.
3. Email the generated report to the dba group for further action.

Issues:

1. You need to increase the snapshot retention in the db to atleast 3 months.
2. If the snapshot retention is too low, the predictions may have huge variations to expected actual growth of db.
3. If the available data in the dba_hist_tbsp_space_usage is scanty, the projections may not be near accurate.
4. If for some reason, the snapshots generation stopped, this report may not work as expected.

Usage:

Pl run this report in a lower environment and make necessary changes as applicable in your case. We are running Oracle 11G on linux OS. Pls modify the email command as applicable to your OS. This script work for Oracle rdbms, and was tested in version 11G. If this script is to be used in a different version of Oracle rdbms, pls make necessary modifications to run. As per you convenience/requirement, the flagged message "ADD SPACE" can be changed to display a meaningful message.

Owing to many limitations, there will be a fair chance of variation in the projected growth of the db. And yet, I will have a starting point as to how much diskspace, I need to procure for my db's future growth. Few deviations this way that way are ok, as I may not be caught off the guard with huge diskspace requirements.

connect system/veeksha
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  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
prompt "!ls -1tr *csv|tail -1|awk '{print "echo|mail -s #"$1" from $ORACLE_SID# -a "$1" #orcldba@orclprod.net#"}'|sed 's/#/"/g'|ksh"

Happy Scripting.

References:
http://www.toadworld.com/platforms/oracle/w/wiki/10837.tablespace-growth-report.aspx
http://www.databasejournal.com/features/oracle/article.php/3673616

Modified Nov 22nd 2017