space_usage.sql

rem Script Description: Prints the amount of tablespace, grouped by segment type
rem                     consumed by each user. It is invaluable in planning database
rem                     reorganization in which index segments will be separated from
rem                     corresponding data segments.  Designed to simplify correct
rem                     choice of database tablespace sizes.
rem
rem Output file:        spaceusage.lis
rem
rem Prepared By:        Oracle Resource Stop
rem
rem Usage Information:  SQLPLUS SYS/pswd
rem                     @spaceusage.sql
rem

set echo off
set pagesize 55
set linesize 80
set newpage 0
set feedback on

spool spaceusage.lis

ttitle -
  left 'Space Usage per Segment Type per User per Tablespace' skip 2

col owner           format a10
col tablespace_name format a20            heading 'Tablespace|Name'
col segment_name    format a20            heading 'Seg|Name'
col segment_type    format a10            heading 'Seg|Type'
col mbytes          format 999,990.99 heading 'Mb Used'

break on tablespace_name skip 2
compute sum of mbytes on segment_type
compute sum of mbytes on tablespace_name
compute sum of mbytes on report
--
SELECT 
     tablespace_name,
     segment_name,
     owner,
     segment_type,
     sum(bytes)/1048756   mbytes
FROM
     DBA_SEGMENTS
GROUP BY
     tablespace_name,
     segment_name,
     owner,
     segment_type
ORDER BY
     tablespace_name,
     segment_name,
     owner,
     segment_type;
spool off;
clear breaks
clear buffer
clear columns
clear computes
set echo on
set feedback on