/* ************************************************ */
/*                                                  */
/* fnctn:   summarize tablespace usage per segment  */
/*          type per user                           */
/*                                                  */
/* name:    @c:\oracle\sql\tssusage.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */
 
spool tssusage.rslt

set linesize  75 
set newpage    0
set pagesize  60 
set trimout   on
set verify    on
set space     10
set underline =
set echo      off
set termout   off

whenever sqlerror exit failure
ttitle center -
'TSSUSAGE - Tablespace Per Segment Type Per User Summary' -
skip 1 left "PAGE:" sql.pno format 999 right CWS_DATE skip 2
 
col username        format a8             heading 'Username'     justify c
col tablespace_name format a14            heading 'Tablespace'   justify c
col segment_type    format a12            heading 'Segment Type' justify c
col mbytes          format 999,990.99     heading 'MB Used'      justify c
 
break on username skip 1
 
select
  owner                  username,
  segment_type           segment_type,
  sum(bytes)/1048576     mbytes,
  tablespace_name        tablespace_name
from
  sys.dba_segments
group by
  owner,
  tablespace_name,
  segment_type
order by
  owner,
  segment_type,
  tablespace_name;
 
clear col;
clear break;
spool off;
