Title: Tablespace Growth Analysis

Author:Richard Larocque, a database administrator for Ottawa Hospital, in Ottawa,
Ontario, Canada.

Use this script to analyze tablespace growth for the last month, one week at a time. The script takes the data from the table
SPACE_CHECK which is defined as follows: tablespace_name varchar2(15), free_space number(10), total_space
number(10), timestamp date. The data is inserted once a day into SPACE_CHECK by running a CRON job. The data is
collected by running the following SQL statement. Insert into space_check select a.tablespace_name, sum(a.bytes)
total_space, sum(b.bytes) free_space,sysdate timestamp from dba_data_files a,dba_free_space b where a.tablespace_name =
b.tablespace_name group by a.tablespace_name;

REM Script to analyze tablespace growth for the last month.
REM Takes the data from the table SPACE_CHECK which is defined
REM as follows: tablespace_name varchar2(15), free_space number(10),
REM             total_space number(10), timestamp date.
REM The data is inserted once a day into SPACE_CHECK by running a CRON job.
REM The data is collected by running the following SQL statement.
REM Insert into space_check
REM select a.tablespace_name, sum(a.bytes) total_space,
REM  sum(b.bytes) free_space,sysdate timestamp
REM  from dba_data_files a,dba_free_space b
REM  where a.tablespace_name = b.tablespace_name
REM  group by a.tablespace_name;
REM Created by Richard Larocque on December 18, 1998.

spool crpgrowth;
set pagesize 24
set feed off;
ttitle 'Tablespace Growth Analysis Report'
column col1 format 99.99 heading 'Today'
column col2 format 99.99 heading '1 week ago'
column col3 format 99.99 heading '2 wks ago'
column col4 format 99.99 heading '3 wks ago'
column col5 format 99.99 heading '4 wks ago'
column col6 format 99 heading '% last week';
select distinct a.tablespace_name,
   a.free_space/a.total_space*100 col1,
   b.free_space/b.total_space*100 col2,
   c.free_space/c.total_space*100 col3,
   d.free_space/d.total_space*100 col4,
   e.free_space/e.total_space*100 col5,
   (1-(a.free_space/a.total_space)/(b.free_space/b.total_space))*100 col6
from space_check a, space_check b, space_check c, space_check d, space_check e
where trunc(a.timestamp) = trunc(sysdate)
  and trunc(b.timestamp(+)) = trunc(sysdate-7)
  and b.tablespace_name (+) = a.tablespace_name
  and trunc(c.timestamp(+)) = trunc(sysdate-14)
  and c.tablespace_name (+) = a.tablespace_name
  and trunc(d.timestamp(+)) = trunc(sysdate-21)
  and d.tablespace_name (+) = a.tablespace_name
  and trunc(e.timestamp(+)) = trunc(sysdate-28)
  and e.tablespace_name (+) = a.tablespace_name
order by col6
/
spool off;