Tablespace Usage Growth Report Scripts

Some useful observer while surfing which can be used to calculate/estimate tablespace growth report.

1.++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set serverout on
set verify off
DECLARE
v_ts_id number;
--v_ts_name varchar2(200) := UPPER('Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN

FOR v_rec in v_cur
LOOP

SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024/1024,2) into v_ts_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;

DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace' );
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('Current Allocated Space: '||v_ts_allocated_space||' GB');
DBMS_OUTPUT.PUT_LINE('Current Used Space: '||v_ts_end_size||' GB');
DBMS_OUTPUT.PUT_LINE('% Used Space: '||round(v_ts_end_size/v_ts_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('Used Size on '||v_begin_snap_date||': '||v_ts_begin_size||' GB' );
DBMS_OUTPUT.PUT_LINE('Used Size on '||v_end_snap_date||': '||v_ts_end_size||' GB' );
DBMS_OUTPUT.PUT_LINE('Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||' : '||v_ts_growth||' GB');
DBMS_OUTPUT.PUT_LINE('Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' GB');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' GB');
DBMS_OUTPUT.PUT_LINE('Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' GB');
DBMS_OUTPUT.PUT_LINE('Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' GB');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\');
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace you entered does not exist');


END;

/
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2. 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;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

3. 

et 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;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

4.

Script for Single Tablespace

##############################################
set serverout on
set verify off
DECLARE
v_ts_id number;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(tablespace_size*v_ts_block_size/1024/1024/1024,2) into v_ts_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(tablespace_usedsize*v_ts_block_size/1024/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(tablespace_usedsize*v_ts_block_size/1024/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('Current Allocated Space: '||v_ts_allocated_space||' GB');
DBMS_OUTPUT.PUT_LINE('Current Used Space: '||v_ts_end_size||' GB');
DBMS_OUTPUT.PUT_LINE('% Used Space: '||round(v_ts_end_size/v_ts_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('Used Size on '||v_begin_snap_date||': '||v_ts_begin_size||' GB' );
DBMS_OUTPUT.PUT_LINE('Used Size on '||v_end_snap_date||': '||v_ts_end_size||' GB' );
DBMS_OUTPUT.PUT_LINE('Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||' : '||v_ts_growth||' GB');
DBMS_OUTPUT.PUT_LINE('Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' GB');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' GB');
DBMS_OUTPUT.PUT_LINE('Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' GB');
DBMS_OUTPUT.PUT_LINE('Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' GB');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace you entered does not exist');
END;
/

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

4. 

select  thedate,
 gbsize,
 prev_gbsize,
 gbsize-prev_gbsize diff
from        (
 select  thedate,
 gbsize,
 lag(gbsize,1) over (order by r) prev_gbsize
 from    (
 select  rownum r,
 thedate,
 gbsize
 from    (
 select  trunc(thedate) thedate,
 max(gbsize) gbsize
 from    (
 select  to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,
 round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize
 from    dba_hist_tbspc_space_usage usage,
 v$tablespace               tablespace,
 dba_hist_snapshot          snapshot,
 v$parameter                block_size
 where   usage.snap_id       = snapshot.snap_id
 and     usage.tablespace_id = tablespace.ts#
 and     tablespace.name     = '&tablespace'
 and     block_size.name     = 'db_block_size'
 )
 group by
 trunc(thedate)
 order by
 trunc(thedate)
 )
 )
 );

select 
b.name,
to_char(c.begin_interval_time,'DD/MM/YY') StartingDate,
round(max(a.tablespace_size*d.block_size)/1024/1024/1024) tblsize_in_MB,
round(max(a.tablespace_maxsize*d.block_size)/1024/1024/1024) totalsize_in_MB,
round(max(a.tablespace_usedsize*d.block_size)/1024/1024/1024) usedsize_in_MB
from DBA_HIST_TBSPC_SPACE_USAGE a, v$tablespace b,
DBA_HIST_SNAPSHOT c, dba_tablespaces d
where a.tablespace_id=b.ts#
and a.snap_id=c.snap_id
and b.name=d.tablespace_name
and b.name in ('APPS_TS_TX_IDX','APPS_TS_TX_DATA')
and to_char(c.begin_interval_time,'DD/MM/YY') = '16/04/14'
group by b.name,to_char(c.begin_interval_time,'DD/MM/YY')
order by 3,2,1;