Tablespace Growth Report

Author JP Vijaykumar

        Date       Sept  8  2013

        Modified   Sept 14  2013

/* The period range for this report is dependent on

the snapshot retention period set for the db. */

–TABLESPACE GROWTH REPORT(USING PL/SQL PROCEDURE)

set serverout on size 1000000 timing on

declare

v_num number;

begin

dbms_output.put_line('DB_NAME,RUN_DATE,TS_NAME,ALLOC_GB,CURR_USED_GB,PREV_USED_GB,VARIANCE,%CHANGE');

for c1 in (select name,tablespace_name from dba_tablespaces,v$database 

           where (tablespace_name like '%DATA%' or

                  tablespace_name like '%INDEX%' ) 

           order by tablespace_name) loop

v_num :=0;

for c2 in (

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 = c1.tablespace_name

and ts.name = dt.tablespace_name order by 1) loop

if (v_num = 0) then

dbms_output.put_line(c1.name||','||c2.run_time||' ,'||c2.name||', '||c2.alloc_size_gb||', '||c2.used_size_gb||' ,'||v_num||','||c2.used_size_gb||', 0 %');

elsif (v_num < c2.used_size_gb) then

dbms_output.put_line(c1.name||','||c2.run_time||', '||c2.name||', '||c2.alloc_size_gb||' ,'||c2.used_size_gb||' ,'||v_num||','||(c2.used_size_gb – v_num)||', '|

                           round((c2.used_size_gb - v_num)*100/v_num,2)||' %');

elsif (v_num > c2.used_size_gb) then

dbms_output.put_line(c1.name||','||c2.run_time||', '||c2.name||', '||c2.alloc_size_gb||', '||c2.used_size_gb||', '||v_num||','||(c2.used_size_gb – v_num)||', -'|

                           round((v_num - c2.used_size_gb)*100/v_num,2)||' %');

else

dbms_output.put_line(c1.name||','||c2.run_time||' ,'||c2.name||' ,'||c2.alloc_size_gb||' ,'||c2.used_size_gb||','||v_num||',0, 0 %');

end if;

v_num:=c2.used_size_gb;

end loop;

end loop;

end;

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

–TO MAIL THE REPORT AS AN ATTACHMENT

!ls -1tr *csv|tail -1|awk '{print "uuencode " $1,$1 "|mailx -s $ORACLE_SID jp[.]vijaykumar[@]gmail[.]com"}'|ksh