— Find Tablespace growth size in number of days.
set feed off
set pages 1000 lines 180
column “tablespace_name” heading “Tablespace | Name” format a20
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’) and ts.tsname=’INTERFACE’
group by
to_char (sp.begin_interval_time,’dd-mm-yyyy’), ts.tsname
order by ts.tsname, days;
—Query shows where purging done
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;
