-- Wrote by Stephen Lee
set serveroutput on
prompt Wait ....
declare
	free_bytes number := 0.0;
	tspace_size number := 0.0;
	total_size number := 0.0;
	total_free number := 0.0;
	max_free number := 0.0;
	max_tab_next number := 0.0;
	max_ind_next number := 0.0;
	max_next number := 0.0;
	thingy varchar2(10);
	cursor c1 is select tablespace_name from dba_tablespaces;
begin
	dbms_output.enable(1000000);
	dbms_output.put_line(rpad('TABLE SPACE',18,' ')||rpad('SIZE',16,' ')||rpad('TOTAL FREE',16,' ')||rpad('MAX FREE',16)||'MAX NEXT');
	dbms_output.put_line(rpad('-',17,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',13,'-'));
	for tsname in c1 loop
		select sum(nvl(bytes,0)) into tspace_size from dba_data_files where tablespace_name = tsname.tablespace_name;
		select max(nvl(a.bytes,0)) into max_free from dba_free_space a, dba_tablespaces b
			where a.tablespace_name(+) = b.tablespace_name and b.tablespace_name = tsname.tablespace_name;
		select sum(nvl(a.bytes,0)) into free_bytes from dba_free_space a, dba_tablespaces b
			where a.tablespace_name(+) = b.tablespace_name and b.tablespace_name = tsname.tablespace_name;
		select max(nvl(next_extent,0)) into max_tab_next from dba_tables where tablespace_name = tsname.tablespace_name;
		select max(nvl(next_extent,0)) into max_ind_next from dba_indexes where tablespace_name = tsname.tablespace_name;
		if max_tab_next > max_ind_next then
			max_next := max_tab_next;
		else
			max_next := max_ind_next;
		end if;
		if max_tab_next is null and max_ind_next is not null then
			max_next := max_ind_next;
		end if;
		if max_ind_next is null and max_tab_next is not null then
			max_next := max_tab_next;
		end if;
		if max_next > max_free then
			thingy := '<';
		else
			thingy := '';
		end if;
		dbms_output.put_line(rpad(tsname.tablespace_name,17,' ')||rpad(to_char(tspace_size,'999,999,999,999'),16,' ')||
			rpad(to_char(free_bytes,'999,999,999,999'),16,' ')||rpad(to_char(max_free,'999,999,999,999'),16,' ')||
			rpad(to_char(max_next,'9,999,999,999'),14,' ')||thingy);
		total_size := total_size + tspace_size;
		total_free := total_free + free_bytes;
	end loop;
	dbms_output.put_line(rpad('-',17,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',15,'-')||' '||rpad('-',13,'-'));
	dbms_output.put_line(rpad('TOTALS',17,' ')||rpad(to_char(total_size,'999,999,999,999'),16,' ')||
		rpad(to_char(total_free,'999,999,999,999'),16,' '));
end;
/
