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

2 Replies to “Script for checking tablespace growth”

  1. 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

    1. It seems some notepad to wordpress character conversion problem..I am checking if I can attach text files.Thanks for informing.

Leave a Reply

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