/* 
   Author    : MSM
   Date      : 23/01/06
   Procedure : To Activate Info on daywise free space, usage - DB
	       growth except for tempfile

   After running the steps 1,2,3,4,5 Succesfully, you are ready to get 
   the Informations about daily space usage - database growth - This 
   can be installed on any Oracle versions.

   ** If you do not want to run as other than sys, system, then 
   ** do not issue grants to public

*/

1.  Connect as system and create a user or select existing user and issue grants
================================================================================

connect &system

Create user MSM identified by MSM
default tablespace users
temporary tablespace temp;

grant select on dba_data_files to msm;
grant select on dba_extents to msm;
grant select on dba_free_space to msm;
grant select on dba_jobs to msm;

grant connect, resource to msm;

2. Create a table tbsp_usage, space_usage procedure and issue grants to public 
===============================================================================

a) connect msm/msm@your_db

Create table tbsp_usage
(
YYYYMM     CHAR(6),
TBSP_NAME  CHAR(15),
TOTSZ      NUMBER(8),
DAY01      NUMBER(8),
DAY02      NUMBER(8),
DAY03      NUMBER(8),
DAY04      NUMBER(8),
DAY05      NUMBER(8),
DAY06      NUMBER(8),
DAY07      NUMBER(8),
DAY08      NUMBER(8),
DAY09      NUMBER(8),
DAY10      NUMBER(8),
DAY11      NUMBER(8),
DAY12      NUMBER(8),
DAY13      NUMBER(8),
DAY14      NUMBER(8),
DAY15      NUMBER(8),
DAY16      NUMBER(8),
DAY17      NUMBER(8),
DAY18      NUMBER(8),
DAY19      NUMBER(8),
DAY20      NUMBER(8),
DAY21      NUMBER(8),
DAY22      NUMBER(8),
DAY23      NUMBER(8),
DAY24      NUMBER(8),
DAY25      NUMBER(8),
DAY26      NUMBER(8),
DAY27      NUMBER(8),
DAY28      NUMBER(8),
DAY29      NUMBER(8),
DAY30      NUMBER(8),
DAY31      NUMBER(8),
TODAYS     NUMBER(8),
PREDAYS    NUMBER(8)
);

grant all on tbsp_usage to public;

b) Create the Procedure space_usage and grant execute to public
-----------------------------------------------------------

create or replace procedure space_usage is
cursor sz is
    select
	a.tablespace_name, sum(round(a.bytes/(1024))) totsz, 
	sum(used) used, sum(nvl(free,0)) free
    from 
	sys.dba_data_files a, 
	(
	select 
		file_id, round(sum(bytes)/(1024)) used
     	from 
		sys.dba_extents 
	group by 
		file_id
	) b,
    	(
	select 
		tablespace_name, file_id, round(sum(bytes)/(1024)) free
    	from 
		sys.dba_free_space 
	group by 
		tablespace_name, file_id
	) c
where
    	a.file_id=b.file_id and 
	a.file_id=c.file_id(+) 
group by 
	a.tablespace_name;

srl    number;
dd     char(2);
yymm   char(6);
strsql varchar2(200);
predd  char(2);
preym  char(6);
usedmb number(7);

begin

    dd:=to_char(sysdate,'dd');
    yymm:=to_char(sysdate,'yyyymm');

    predd:=to_char(trunc(sysdate)-1,'dd');

    if dd='01' then
       preym:=to_char(trunc(sysdate)-1,'yyyymm');
    else
       preym:=to_char(trunc(sysdate),'yyyymm');
    end if;

    -- dbms_output.put_line('preym='||preym||' predd='||predd);

    for r in sz loop 
	  begin
		select 1 into srl 
		from tbsp_usage
		where 
		yyyymm=to_char(sysdate,'yyyymm') and 
		trim(tbsp_name)=trim(r.tablespace_name);

	        strsql:='update tbsp_usage 
			 set day'||dd||'=:1
		   	 where yyyymm='||yymm||' and 
		   	 trim(tbsp_name)=:2 and 
		   	 day'||dd||' is null';

	  	execute immediate strsql 
			using r.used, trim(r.tablespace_name);

	        begin
 	               strsql:='select day'||dd||' from tbsp_usage
		     		where yyyymm='||yymm||' and 
		         	trim(tbsp_name)=:1';

	 	       execute immediate strsql into usedmb 
		               using r.tablespace_name;

	               exception when no_data_found then
		       usedmb:=0;
             	end;

	        strsql:='update tbsp_usage 
			 set todays=:1, predays=:2
		   	 where yyyymm='||yymm||' and 
		   	 trim(tbsp_name)=:3'; 

		execute immediate strsql 
			using r.used, usedmb, trim(r.tablespace_name);

	        exception when no_data_found then
          	strsql := 'insert into tbsp_usage(yyyymm,tbsp_name,totsz,day'||dd||') '|| 
                	  'values(:1,:2,:3,:4)';
	  	execute immediate strsql 
			using yymm,r.tablespace_name,r.totsz,r.used;
	   end;
   end loop;
   commit;
end;
/

grant execute on msm.space_usage to public;

3. Schedule the job (To run above procedure as per convinience. Say Daily Morning 7 O'clock)
============================================================================================

variable jobno number;
exec dbms_job.submit(:jobno,'MSM.SPACE_USAGE;',trunc(sysdate)+1+7/24,'trunc(sysdate)+1')

*===========*
! Reporting !
*===========*

4. Keep the following in a file free.sql
========================================

break on report
compute sum of free on report
compute sum of used on report
compute sum of totsz on report
column "(%)" format 999
set feedback off;
set pages 24
select
     a.tablespace_name, sum(round(a.bytes/(1024*1024))) totsz, sum(used) used, 
     sum(nvl(free,0)) free, 
     round((sum(nvl(free,0))/sum(round(a.bytes/(1024*1024))))*100) "(%)"
from 
    dba_data_files a, 
    (select file_id,round(sum(bytes)/(1024*1024)) used
     from dba_extents group by file_id) b,
    (select tablespace_name,file_id,round(sum(bytes)/(1024*1024))  free
    from dba_free_space group by tablespace_name, file_id) c
where
    a.file_id=b.file_id and a.file_id=c.file_id(+) 
group
    by a.tablespace_name;
exec msm.space_usage;
@usagerep
@usagemb
set feedback on;

5. Keep the following in a file usagerep.sql
============================================

set wrap off
set lines 500
break on report
compute sum label 'Total' of totsz on report
compute sum of todays on report
compute sum of 1st on report
compute sum of 2nd on report
compute sum of 3rd on report
compute sum of 21st on report
compute sum of 22nd on report
compute sum of 23rd on report
compute sum of 31st on report
compute sum of 4th on report
compute sum of 5th on report
compute sum of 6th on report
compute sum of 7th on report
compute sum of 8th on report
compute sum of 9th on report
compute sum of 10th on report
compute sum of 11th on report
compute sum of 12th on report
compute sum of 13th on report
compute sum of 14th on report
compute sum of 15th on report
compute sum of 16th on report
compute sum of 17th on report
compute sum of 18th on report
compute sum of 19th on report
compute sum of 20th on report
compute sum of 24th on report
compute sum of 25th on report
compute sum of 26th on report
compute sum of 27th on report
compute sum of 28th on report
compute sum of 29th on report
compute sum of 30th on report
column tbsp_name format a10
column totsz format 9999999
column todays format 999999
column "1ST" format 99999
column "21ST" format 99999
column "31ST" format 99999
column "2ND" format 99999
column "22ND" format 99999
column "3RD" format 99999
column "23RD" format 99999
column "4TH" format 99999
column "5TH" format 99999
column "6TH" format 99999
column "7TH" format 99999
column "8TH" format 999999
column "9TH" format 99999
column "10TH" format 99999
column "11TH" format 99999
column "12TH" format 99999
column "13TH" format 99999
column "14TH" format 99999
column "15TH" format 99999
column "16TH" format 99999
column "17TH" format 99999
column "18TH" format 99999
column "19TH" format 99999
column "20TH" format 99999
column "24TH" format 99999
column "25TH" format 99999
column "26TH" format 99999
column "27TH" format 99999
column "28TH" format 99999
column "29TH" format 99999
column "30TH" format 99999
set feedback off
select
	yyyymm, a.tbsp_name, totsz, 
	decode(nvl(predays,0),0, 0,nvl(todays,0)-nvl(predays,0)) todays,
	decode(nvl(day02,0), 0, 0, nvl(day02,0)-nvl(day01,0))  "1ST", 
	decode(nvl(day03,0), 0, 0, nvl(day03,0)-nvl(day02,0))  "2ND",
	decode(nvl(day04,0), 0, 0, nvl(day04,0)-nvl(day03,0))  "3RD", 
	decode(nvl(day05,0), 0, 0, nvl(day05,0)-nvl(day04,0))  "4TH",
	decode(nvl(day06,0), 0, 0, nvl(day06,0)-nvl(day05,0))  "5TH", 
	decode(nvl(day07,0), 0, 0, nvl(day07,0)-nvl(day06,0))  "6TH",
	decode(nvl(day08,0), 0, 0, nvl(day08,0)-nvl(day07,0))  "7TH", 
	decode(nvl(day09,0), 0, 0, nvl(day09,0)-nvl(day08,0))  "8TH",
	decode(nvl(day10,0), 0, 0, nvl(day10,0)-nvl(day09,0))  "9TH", 
	decode(nvl(day11,0), 0, 0, nvl(day11,0)-nvl(day10,0)) "10TH",
	decode(nvl(day12,0), 0, 0, nvl(day12,0)-nvl(day11,0)) "11TH", 
	decode(nvl(day13,0), 0, 0, nvl(day13,0)-nvl(day12,0)) "12TH",
	decode(nvl(day14,0), 0, 0, nvl(day14,0)-nvl(day13,0)) "13TH", 
	decode(nvl(day15,0), 0, 0, nvl(day15,0)-nvl(day14,0)) "14TH",
	decode(nvl(day16,0), 0, 0, nvl(day16,0)-nvl(day15,0)) "15TH", 
	decode(nvl(day17,0), 0, 0, nvl(day17,0)-nvl(day16,0)) "16TH",
	decode(nvl(day18,0), 0, 0, nvl(day18,0)-nvl(day17,0)) "17TH", 
	decode(nvl(day19,0), 0, 0, nvl(day19,0)-nvl(day18,0)) "18TH",
	decode(nvl(day20,0), 0, 0, nvl(day20,0)-nvl(day19,0)) "19TH", 
	decode(nvl(day21,0), 0, 0, nvl(day21,0)-nvl(day20,0)) "20TH",
	decode(nvl(day22,0), 0, 0, nvl(day22,0)-nvl(day21,0)) "21ST", 
	decode(nvl(day23,0), 0, 0, nvl(day23,0)-nvl(day22,0)) "22ND",
	decode(nvl(day24,0), 0, 0, nvl(day24,0)-nvl(day23,0)) "23RD", 
	decode(nvl(day25,0), 0, 0, nvl(day25,0)-nvl(day24,0)) "24TH",
	decode(nvl(day26,0), 0, 0, nvl(day26,0)-nvl(day25,0)) "25TH", 
	decode(nvl(day27,0), 0, 0, nvl(day27,0)-nvl(day26,0)) "26TH",
	decode(nvl(day28,0), 0, 0, nvl(day28,0)-nvl(day27,0)) "27TH", 
	decode(nvl(day29,0), 0, 0, nvl(day29,0)-nvl(day28,0)) "28TH",
	decode(nvl(day30,0), 0, 0, nvl(day30,0)-nvl(day29,0)) "29TH", 
	decode(nvl(day31,0), 0, 0, nvl(day31,0)-nvl(day30,0)) "30TH",
	decode(nvl(nm01,0), 0, 0, nvl(nm01,0)-nvl(day31,0)) "31ST"
from
   	msm.tbsp_usage a, 
   	(
	select 
		b.tbsp_name, b.day01 nm01 
	from 
		msm.tbsp_usage b 
    	where 
		b.yyyymm=decode (
				to_number(substr(b.yyyymm,5,2))-12,0,
				to_char(to_number(b.yyyymm)+89,'000000'),
				to_char(to_number(b.yyyymm)+1,'000000')
				)
	) c
where
	a.tbsp_name=c.tbsp_name(+)
	and a.yyyymm=to_char(sysdate,'yyyymm');
CLEAR COLUMNS;
clear breaks;
clear computes;
set feedback on;
set colsep " ";

6. Keep the following in file usagemb.sql
=========================================

set wrap off
set lines 500
break on report
compute sum of totsz on report
compute sum of todays on report
compute sum of day01 on report
compute sum of day02 on report
compute sum of day03 on report
compute sum of day21 on report
compute sum of day22 on report
compute sum of day23 on report
compute sum of day31 on report
compute sum of day04 on report
compute sum of day05 on report
compute sum of day06 on report
compute sum of day07 on report
compute sum of day08 on report
compute sum of day09 on report
compute sum of day10 on report
compute sum of day11 on report
compute sum of day12 on report
compute sum of day13 on report
compute sum of day14 on report
compute sum of day15 on report
compute sum of day16 on report
compute sum of day17 on report
compute sum of day18 on report
compute sum of day19 on report
compute sum of day20 on report
compute sum of day24 on report
compute sum of day25 on report
compute sum of day26 on report
compute sum of day27 on report
compute sum of day28 on report
compute sum of day29 on report
compute sum of day30 on report
column tbsp_name format a10
column totsz format 99999
column todays format 99999
column day01 format 99999
column day02 format 99999
column day03 format 99999
column day04 format 99999
column day05 format 99999
column day06 format 99999
column day07 format 99999
column day08 format 99999
column day09 format 99999
column day10 format 99999
column day11 format 99999
column day12 format 99999
column day13 format 99999
column day14 format 99999
column day15 format 99999
column day16 format 99999
column day17 format 99999
column day18 format 99999
column day19 format 99999
column day20 format 99999
column day21 format 99999
column day22 format 99999
column day23 format 99999
column day24 format 99999
column day25 format 99999
column day26 format 99999
column day27 format 99999
column day28 format 99999
column day29 format 99999
column day30 format 99999
column day31 format 99999
select
	yyyymm, a.tbsp_name, round(totsz/1024) totsz, 
	round(nvl(day01,0)/1024) day01, 
	round(nvl(day02,0)/1024) day02,
	round(nvl(day03,0)/1024) day03, 
	round(nvl(day04,0)/1024) day04,
	round(nvl(day05,0)/1024) day05, 
	round(nvl(day06,0)/1024) day06,
	round(nvl(day07,0)/1024) day07, 
	round(nvl(day08,0)/1024) day08,
	round(nvl(day09,0)/1024) day09, 
	round(nvl(day10,0)/1024) day10,
	round(nvl(day11,0)/1024) day11, 
	round(nvl(day12,0)/1024) day12,
	round(nvl(day13,0)/1024) day13, 
	round(nvl(day14,0)/1024) day14,
	round(nvl(day15,0)/1024) day15, 
	round(nvl(day16,0)/1024) day16,
	round(nvl(day17,0)/1024) day17, 
	round(nvl(day18,0)/1024) day18,
	round(nvl(day19,0)/1024) day19, 
	round(nvl(day20,0)/1024) day20,
	round(nvl(day21,0)/1024) day21, 
	round(nvl(day22,0)/1024) day22,
	round(nvl(day23,0)/1024) day23, 
	round(nvl(day24,0)/1024) day24,
	round(nvl(day25,0)/1024) day25, 
	round(nvl(day26,0)/1024) day26,
	round(nvl(day27,0)/1024) day27, 
	round(nvl(day28,0)/1024) day28,
	round(nvl(day29,0)/1024) day29, 
	round(nvl(day30,0)/1024) day30,
	round(nvl(day31,0)/1024) day31
from
   	msm.tbsp_usage a
where
	a.yyyymm=to_char(sysdate,'yyyymm');

----------------- The End ------------------------------------------

Now connect to sys, system / any other user using sqlplus and run

sql>@free