Estimate Tablespace growth

Some time it is very helpful to plan disk space/ tablespace management if you estimate the growth of your tablespace. Here is a select query which can be helpful :




SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days

, ts.tsname

, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB

, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB

FROM DBA_HIST_TBSPC_SPACE_USAGE tsu

, DBA_HIST_TABLESPACE_STAT ts

, DBA_HIST_SNAPSHOT sp

, DBA_TABLESPACES dt

WHERE tsu.tablespace_id= ts.ts#

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name

AND ts.tsname NOT IN ('SYSAUX','SYSTEM')

GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname

ORDER BY ts.tsname, days;