REM ******************************************************************
REM * @c:\oracle\sql\tblsuse.sql                            
REM *                                                        
REM * Owner: R.Kupcunas                                      
REM * Date:  01-MAY-95                                       
REM *                                                        
REM * Script: tblsuse                                                *
REM * Summarize tablespace usage by segment type and user            *
REM ******************************************************************

 accept owner prompt 'Enter owner name or "ALL": '

  set feedback off

col username         format            a20 justify c heading 'Username'
col tablespace_name  format            a20 justify c heading 'Tablespace Name'
col segment_type     format            a17 justify c heading 'Segment Type'
col mbytes           format 999,999,990.99 justify c heading 'Mbytes used'

 break - 
  on username skip 1 

 select
   owner              username,
   segment_type       segment_type,
   sum(bytes)/1048576 mbytes,          
   tablespace_name    tablespace_name
    from 
     sys.dba_segments
      where owner = upper('&owner') or upper('&owner') = 'ALL'
      group by owner,
               tablespace_name,
               segment_type
                 order by 
               owner,
               segment_type,
               tablespace_name;

 undef owner
 set feedback on





