feespace.sql

rem tablesp.sql
rem
rem Creates a report showing the allocated, used, and free space for every
rem tablespace
rem
set verify off
set echo off
set termout off
set pagesize 9999
set feedback off
rem
rem Calculate blocks to bytes conversion ratio
rem
column dbb noprint new_value mbytes
select value / 1048576 dbb from v$parameter where name = 'db_block_size';
rem
rem Calculate database name
rem
column dbname noprint new_value sid
select name dbname from v$database;
rem
rem Calculate current date for the report
rem
column now noprint new_value today
select to_char(sysdate, 'MM/DD/YY') now from dual;
rem
set termout on
create table tablesp_tmp (
		tablespace_name varchar2(30),
		allocated number,
		used number,
		free number);

declare
	cursor df_cursor is select
		tablespace_name, round(sum(blocks) * &mbytes)
		from sys.dba_data_files
		group by tablespace_name
		order by 1;

	cursor fs_cursor (p_tblsp varchar2) is select
		round(sum(blocks) * &mbytes)
		from sys.dba_free_space
		where tablespace_name = p_tblsp;

	lv_tablespace_name	sys.dba_data_files.tablespace_name%TYPE;
	lv_allocated		number;
	lv_free			number;

	procedure write_out is
	begin
		insert into tablesp_tmp values (
			lv_tablespace_name,
			lv_allocated,
			lv_allocated - lv_free,		/* Megabytes used */
			lv_free);
	end;

begin
	open df_cursor;
	loop
		fetch df_cursor into lv_tablespace_name, lv_allocated;
		exit when df_cursor%NOTFOUND;
		open fs_cursor(lv_tablespace_name);
		fetch fs_cursor into lv_free;
		if fs_cursor%NOTFOUND then
			lv_free := 9999;
		end if;
		close fs_cursor;
		write_out;
	end loop;
	close df_cursor;
end;
/

set termout on
ttitle center 'Tablespace Usage report for database &sid' right '&today' skip 2
col allocated format 999999990 heading "Megabytes|Allocated"
col used format 999999990 heading "Megabytes|Used  "
col free format 999999990 heading "Megabytes|Free  "
col pct format 990 heading "Percent|Free "
break on report
compute sum of allocated used free on report
spool tablesp.lis
select tablespace_name,
	allocated,
	used,
	free,
	decode(allocated, 0, 0, round(100*free/allocated)) pct
	from tablesp_tmp
	order by 1;
spool off
drop table tablesp_tmp;
set termout on
prompt Created tablesp.lis report file...
set termout off
--exit