Tablespace Capacity Planning Report

Tablespace Capacity Planning Report
Author JP Vijaykumar
Written Sep 14 2017
Modified Mar 08 2019
***************************************************************************************************************
This script is provided for enducational purpose only.
The readers are advised to make necessary changes to the script as may be required for their use in their repective environments.
Pls test this script thoroughly in a lower environment, before using in any production db.

This script was generated/tested in Oracle 11G environment.
This script may not work in other versions of Oracle, pls modify the script as may be needed using your own discretion/ingenuity.
***************************************************************************************************************

I need to run capacity planning report in my project.

Requirements:

Predict db growth for 3 months, 6 months and one year.
Display message, if the available free space in each tablespace is less than it's projected growth for the next 3 months period.

Issues:

You need to increase the snapshot retention in the db to 3 months(conservative period).
If the snapshot retention is too low, the predictions may have huge variation on the expected actual growth of db.
If the available data in the dba_hist_tbsp_space_usage is scanty, the projections may not be near accurate.
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.
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 your 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 the actual growth of the db.
And yet, I will have a starting point as to how much diskspace, upfront I need to be procured for my db's future growth.
Few deviations this way that way are ok, as I may not be caught off the guard with sudden huge diskspace requirements.

connect veeksha/Saketh

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;

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