Check tablespace size growth forcast in Oracle

Tablespace Capacity Forecast

Find the table space utilization for future estimation of database growth and allocate/add more hard disk to system before space crunch.

set feedback off
set lines 180
set head on
col “Create Time” format a11
col name format a30
col curr_size_mb format 99999999
col growth_per_day format 99999999.9
col growth_120_days format 99999999.9
col growth_240_days format 99999999.9
col projection_365_days_mb format 99999999
select min(creation_time) “Create Time”,, round(sum(df.bytes)/1024/1024) curr_size_mb,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)),1) growth_per_day,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 120,1) growth_120_days,
round( (sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 240,1) growth_240_days,
round( (sum(bytes)/1024/1024)+((sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time))*365) ) projection_365_days_mb
from V$datafile df ,V$tablespace ts where df.ts#=ts.ts# group by df.ts#, order by df.ts#;



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.