REM	To see the actual space allocated and used for tables and 
REM corresponding indexes
REM
set serveroutput on feedback off echo off verify off lines 80 trims on
spool space.lst
declare
-- v_tablepspace varchar2(32) := 'TS1';
 v_owner varchar2(32) := 'SCOTT';
 
--  cursor ctab is select owner, table_name
-- 		from dba_tables where tablespace_name = v_tablepspace order by owner, table_name
-- 	;

 cursor ctab is select owner, table_name
		from dba_tables where owner = v_owner order by owner, table_name
	;

 cursor cind (wowner in varchar2, wtab in varchar2) is select owner, index_name 
		from dba_indexes
		where table_name = wtab 
		and   table_owner = wowner;

 wtotal_blocks               number;
 wtotal_bytes                number;
 wunused_blocks              number;
 wunused_bytes               number;
 wlast_used_extent_file_id   number;
 wlast_used_extent_block_id  number;
 wlast_used_block            number;

 wtaballoc       number;
 wtabused        number;
 wtabfree        number;
 windalloc       number;
 windused        number;
 windfree        number;

 wtottaballoc       number := 0;
 wtottabused        number := 0;
 wtottabfree        number := 0;
 wtotindalloc       number := 0;
 wtotindused        number := 0;
 wtotindfree        number := 0;

begin

 dbms_output.enable (9999999);
 
 dbms_output.new_line ();
 for rec in (select to_char(sysdate, 'dd Mon yyyy hh24:mi:ss' )||
 	 	 	 		' - User ('||v_owner||') on Database ('||global_name||')' Where_am_i 
					from global_name)
 loop
   dbms_output.put_line (rec.Where_am_i );
 end loop;
 dbms_output.new_line ();
 
 
 dbms_output.put_line ('Table Owner.Name                Tables   Used MB  Free MB  Indexes  Used MB  Free MB' );
 dbms_output.put_line ('------------------------------  -------  -------  -------  -------  -------  -------');

 for rtab in ctab loop

   dbms_space.unused_space (rtab.owner, rtab.table_name, 'TABLE', 
                            wtotal_blocks,  wtotal_bytes,  wunused_blocks,
                            wunused_bytes, wlast_used_extent_file_id,
                            wlast_used_extent_block_id, wlast_used_block);

   wtaballoc := wtotal_bytes/1048576;
   wtabused  := (wtotal_bytes - wunused_bytes)/ 1048576;
   wtabfree  := wunused_bytes/1048576;
   windalloc := 0;
   windused  := 0;
   windfree  := 0;

   wtottaballoc := wtottaballoc + wtaballoc;
   wtottabused  := wtottabused  + wtabused;
   wtottabfree  := wtottabfree  + wtabfree;

   for rind in cind (rtab.owner, rtab.table_name) loop

     dbms_space.unused_space (rind.owner, rind.index_name, 'INDEX', 
                              wtotal_blocks,  wtotal_bytes,  wunused_blocks,
                              wunused_bytes, wlast_used_extent_file_id,
                              wlast_used_extent_block_id, wlast_used_block);
     -- print Index detail!
     dbms_output.put_line (rpad((  --rtab.owner || '.' || 
				  '  '||rind.index_name),30) || 
			   lpad(' ', 27) ||
			   lpad(to_char(wtotal_bytes/1048576, '9999.999'), 9) ||
			   lpad(to_char((wtotal_bytes - wunused_bytes)/ 1048576, '9999.999'), 9) ||
			   lpad(to_char(wunused_bytes/1048576, '9999.999'), 9) );
			 
     windalloc := windalloc + wtotal_bytes/1048576;
     windused  := windused + (wtotal_bytes - wunused_bytes)/ 1048576;
     windfree  := windfree + wunused_bytes/1048576;
  
   end loop;

   wtotindalloc := wtotindalloc + windalloc;
   wtotindused  := wtotindused  + windused;
   wtotindfree  := wtotindfree  + windfree;

   dbms_output.put_line (rpad(( -- rtab.owner || '.' || 
				rtab.table_name),30) || 
			 lpad(to_char(wtaballoc, '9999.999'), 9) ||
			 lpad(to_char(wtabused, '9999.999'), 9) ||
			 lpad(to_char(wtabfree, '9999.999'), 9) ||
			 lpad(to_char(windalloc, '9999.999'), 9) ||
			 lpad(to_char(windused, '9999.999'), 9) ||
			 lpad(to_char(windfree, '9999.999'), 9) );

 end loop;

 dbms_output.put_line ('------------------------------  -------  -------  -------  -------  -------  -------');

 dbms_output.put_line (rpad('TOTAL',30) || 
        		 lpad(to_char(wtottaballoc, '99999.999'), 9) ||
			 lpad(to_char(wtottabused, '99999.999'), 9) ||
			 lpad(to_char(wtottabfree, '99999.999'), 9) ||
			 lpad(to_char(wtotindalloc, '99999.999'), 9) ||
			 lpad(to_char(wtotindused, '99999.999'), 9) ||
			 lpad(to_char(wtotindfree, '99999.999'), 9) );
 dbms_output.put_line ('------------------------------------------------------------------------------------');

     dbms_output.put_line ('Full segment type breakdown is:');
     dbms_output.put_line ('-------------------------------');
 for j in (select rpad(segment_type, 20) seg_type, sum(bytes) / 1048576 All_Seg_Types 
           from dba_segments 
	   where owner = v_owner
	   group by rpad(segment_type, 20)) loop
     dbms_output.put_line (
     	j.seg_type||' '||
     	to_char(j.All_Seg_Types, '99999.99'));
 end loop;
     dbms_output.put_line ('                       --------');
 for k in (select sum(bytes) / 1048576 All_Seg_Types 
           from dba_segments 
	   where owner = v_owner) loop
     dbms_output.put_line ('Total                '||to_char(k.All_Seg_Types, '99999.99'));
 end loop;
end;
/
set feedback on verify on 
spool off