Script for checking tablespace growth

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('USERS')
 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

About the Author

debasis maity

12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA

2 thoughts on “Script for checking tablespace growth

    • Author gravatar

      I tried to run for all tablespaces from a given base and received this error below, please see.

      SQL> 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 = 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;
      2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 and ts.name = dt.tablespace_name )
      *
      ERROR at line 12:
      ORA-00928: missing SELECT keyword

Leave a Reply

Your email address will not be published. Required fields are marked *