Hi guys, I will be sharing a great script which will help you identify and project the growth of tablespace in your databases.
--TABLESPACE GROWTH REPORT(USING SQLPLUS) set linesize 120 column name format a15 column variance format a20 alter session set nls_date_format='yyyy-mm-dd'; with t 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 =upper('&TABLESPACE_NAME') and ts.name = dt.tablespace_name ) select e.run_time,e.name,e.alloc_size_gb,e.used_size_gb curr_used_size_gb,b.used_size_gb prev_used_size_gb, case when e.used_size_gb > b.used_size_gb then to_char(e.used_size_gb - b.used_size_gb) when e.used_size_gb = b.used_size_gb then 'NO DATA GROWTH' when e.used_size_gb < b.used_size_gb then '***DATA PURGED' end variance from t e, t b where e.run_time = b.run_time +1 order by 1;
The output is shown as below.
So by knowing the daily increase/decrease we can estimate the average usage of space monthly,quarterly ,etc.
This proves helpful in allocating appropriate space before hand and avoid space-crunch situations.
I’m sure this script will help you guys with your tablespace management.
Great article , in those statement parts “su.tablespace_sizedt.block_size” or “su.tablespace_usedsizedt.block_size” something missing
LikeLike
Yes, the expression should be su.tablespace_size*dt.block_size. I have updated it in the blog. Thanks for pointing out the mistake.
LikeLike