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

  cursor ctab is 
        select a.owner, a.table_name, b.extents
 		from dba_tables a, dba_segments b
		where a.TABLE_NAME = b.segment_name
		and a.OWNER = b.owner
		and a.owner = v_owner 
		order by a.owner, a.table_name
 	;

 cursor cind (wowner in varchar2, wtab in varchar2) is 
        select a.owner, a.index_name, b.extents
		from dba_indexes a, dba_segments b
		where a.INDEX_NAME = b.segment_name
		and a.owner = b.owner
		and a.table_name = wtab 
		and a.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)
 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 ('Expanded space report for User :'|| v_owner );
  dbms_output.put_line ('--------------------------------'|| rpad('-', length(v_owner), '-'));
  dbms_output.put_line ('Table Owner.Name(extents)             Tab.   UsedM'||
                        '  FreeM  Ind.   UsedM  FreeM' );
  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||'('||rind.extents||')'),36) || 
			   lpad(' ', 23) ||
			   lpad(to_char(wtotal_bytes/1048576, '9999.9'), 7) ||
			   lpad(to_char((wtotal_bytes - wunused_bytes)/ 1048576, '9999.9'), 7) ||
			   lpad(to_char(wunused_bytes/1048576, '9999.9'), 7) );
			 
     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 ||'('||rtab.extents||')' ),36) || 
			 lpad(to_char(wtaballoc, '9999.9'), 7) ||
			 lpad(to_char(wtabused, '9999.9'), 7) ||
			 lpad(to_char(wtabfree, '9999.9'), 7) ||
			 lpad(to_char(windalloc, '9999.9'), 7) ||
			 lpad(to_char(windused, '9999.9'), 7) ||
			 lpad(to_char(windfree, '9999.9'), 7) );

 end loop;

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

 dbms_output.put_line (rpad('TOTAL',36) || 
        		 lpad(to_char(wtottaballoc, '99999.9'), 7) ||
			 lpad(to_char(wtottabused, '99999.9'), 7) ||
			 lpad(to_char(wtottabfree, '99999.9'), 7) ||
			 lpad(to_char(wtotindalloc, '99999.9'), 7) ||
			 lpad(to_char(wtotindused, '99999.9'), 7) ||
			 lpad(to_char(wtotindfree, '99999.9'), 7) );

 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