report showing free space per tablespace over time

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Wed, 26 Jun 2002 12:49:44 -0800
Message-ID: <F001.00489570.20020626124944@fatcity.com>

Listers,

Ever been asked how much free space you have in the tablespaces over time? I wrote a tool that tracks and reports on free space per tablespace over time and thought I would share it.

It consists of a simple stored procedure that stores values in a holding table and a report that will tell you how much each tablespace had over the past week (you can change the coverage of this report.)

1. Here is the stored procedure:

CREATE OR REPLACE PROCEDURE
tablespace_proc AS

	v_errmsg varchar2(100);
	v_errcode varchar2(100);
       

BEGIN

	delete dbmon.dbmon_tablespace_stats
	where trunc(record_date) = trunc(sysdate);

INSERT INTO dbmon.dbmon_tablespace_stats         

(tablespace_name,

	total_space, 
	megs_free, 
	max_extent, 
	autoextend,
	record_date)

SELECT
	fs.tablespace_name,
	round(df.total_bytes/1024/1024,0),
	round(fs.bytes_free/1024/1024,0),
	round(fs.max_bytes/1024/1024,0),
	decode(a.tablespace_name,null,'No','Yes'),
	sysdate
FROM


	(SELECT

		tablespace_name,
		SUM(bytes) bytes_free,
		max(bytes) max_bytes
	FROM
		dba_free_space fs
	GROUP BY
		tablespace_name) fs,


	(SELECT

		tablespace_name,
		sum(bytes) total_bytes
	FROM
		dba_data_files
	GROUP BY 
		tablespace_name) df,


	(SELECT DISTINCT

		tablespace_name
	FROM
		dba_data_files
	WHERE
		autoextensible = 'YES') a,


	(select tablespace_name

	from dba_tablespaces) ts
WHERE
	df.tablespace_name = fs.tablespace_name(+)
AND
	df.tablespace_name = a.tablespace_name(+)
AND 	
	df.tablespace_name = ts.tablespace_name;

commit;

exception

        when others then         

	v_errmsg := substr(SQLERRM,1,100);
	v_errcode := SQLCODE;

	insert into dbmon_activity_log
		(activity_date,
		activity_desc,
		procedure_name,
		error_code,
		error_msg,
		error_date)
	values
		(sysdate,
		'tablespace_proc',
		'tablespace_proc',
		v_errcode,
		v_errmsg,
		sysdate);

commit;

end;
/

2) Here is the code to submit it to the job scheduler (every day at 5AM):

variable jobno number

exec sys.dbms_job.submit(job=>:jobno, what=>'begin dbmon.dbmon_tablespace_proc;end;',
next_date=>trunc(sysdate+1)+5/24,interval=>'trunc(sysdate+1)+5/24');

3) and here is the report:

@save_sqlplus_settings

set term off

set head off

spool temp.sql

select 'col c'||rownum ||' for 999,999,990 head "'|| to_char(sysdate+1-rownum,'dd-mon')||'"' from dba_tablespaces where rownum<8;

select 'compute sum of c'||rownum ||' on report' from dba_tablespaces where rownum<8;

select 'break on report' from dual;

spool off

@temp.sql

exec dbmon.dbmon_tablespace_proc;

set term on
set lines 135

prompt
prompt Free space per tablespace:
prompt

select tablespace_name,

	sum(decode(old,7,value)) c7,
	sum(decode(old,6,value)) c6,
	sum(decode(old,5,value)) c5,
	sum(decode(old,4,value)) c4,
	sum(decode(old,3,value)) c3,
	sum(decode(old,2,value)) c2,
	sum(decode(old,1,value)) c1
from


	(select tablespace_name, 

		megs_free value, 
		decode 
		(trunc(record_date),
		trunc(sysdate),1,
		trunc(sysdate)-1,2,
		trunc(sysdate)-2,3,
		trunc(sysdate)-3,4,
		trunc(sysdate)-4,5,
		trunc(sysdate)-5,6,
		trunc(sysdate)-6,7) old
	from 
		dbmon.dbmon_tablespace_stats)

group by tablespace_name
order by 1
/

good luck,

Jack

---

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jun 26 2002 - 15:49:44 CDT