/*****************************************
* File:    db_space.sql 
* Author:  P.Noonan (SGA Consulting Ltd )
* Purpos: Reports Tablespace name,
*                 Tablespace size (MB),
*		  Free space in Tablespace (MB),
*		  % Free in Tablespace,
*		  Largest contiguous free space in Tablespace (MB),
*		  Largest next extent in tablespace (MB)    
******************************************/
set serveroutput on size 5000 
begin

declare
   cursor c_tbs is
   select tablespace_name tablespace_name, 
          sum(bytes) / 1048576 size_mb
     from dba_data_files
 group by tablespace_name; 

   cursor c_free ( p_tablespace dba_data_files.tablespace_name%type ) is
   select sum(bytes) / 1048576 free_mb,
          max(bytes) / 1048576 l_contig_free_mb
     from dba_free_space
    where tablespace_name = p_tablespace;

   cursor c_lnext ( p_tablespace dba_tablespaces.tablespace_name%type ) is
   select max(next_extent) / 1048576 l_next_mb
     from dba_segments
    where tablespace_name = p_tablespace;

   c_free_rec  c_free%rowtype;
   c_lnext_rec c_lnext%rowtype;
   v_pct_free  integer(3);

begin

  dbms_output.put(rpad('Tablespace',20));
  dbms_output.put('Size MB');
  dbms_output.put(lpad('Free MB',10));
  dbms_output.put('  ');
  dbms_output.put('Pct Free');
  dbms_output.put('  ');
  dbms_output.put(lpad('L Free MB',9));
  dbms_output.put('  ');
  dbms_output.put_line('L Next MB');

  dbms_output.put(rpad('----------',20));
  dbms_output.put('-------');
  dbms_output.put(lpad('-------',10));
  dbms_output.put('  ');
  dbms_output.put('--------');
  dbms_output.put('  ');
  dbms_output.put(lpad('---------',9));
  dbms_output.put('  ');
  dbms_output.put_line('---------');
  for c_tbs_rec in c_tbs
  loop
    open c_free ( c_tbs_rec.tablespace_name );
    fetch c_free into c_free_rec;
    close c_free;
    open c_lnext ( c_tbs_rec.tablespace_name );
    fetch c_lnext into c_lnext_rec;
    close c_lnext;
    v_pct_free := floor((c_free_rec.free_mb / c_tbs_rec.size_mb ) * 100);
    dbms_output.put_line(
                         rpad(c_tbs_rec.tablespace_name,20)||' '||
                         lpad(c_tbs_rec.size_mb,6)||
                         lpad(to_char(c_free_rec.free_mb,9999.99),10)||'  '||
                         lpad(v_pct_free,8)||'  '||
                         lpad(to_char(c_free_rec.l_contig_free_mb,9999.99),9)
                         ||'  '||
                         lpad(to_char(c_lnext_rec.l_next_mb,9999.99),9));
  end loop;

end;

end;
/