Size of a schema (approx.)

Shows the owner and size of the schema (approximate only).

select owner username, round(sum(bytes)/1048576,2) mbytes
from dba_segments
where owner not in ('SYS','SYSTEM','OUTLN','DBADMIN','DBSNMP','TSMSYS')
group by owner
order by owner

Show the size in MB of a schema grouped by tablespace and segment type.
This is only approximate.

--Setup the columns, breaks, and total
col username format a20 justify c heading 'Username'
col tablespace_name format a20 justify l heading 'Tablespace Name'
col segment_type format a17 justify l heading 'Segment Type'
col mbytes format 999,999,990.99 justify l heading 'MbUsed'

--now break on username so it doesn't repeat and break on report for the compute
break on username skip 1 on report

-- compute the total mega bytes
compute sum label total: of mbytes on report

select owner username, segment_type, sum(bytes)/1048576 mbytes,
tablespace_name tablespace_name
from dba_segments
where owner = 'CXDEV'
group by owner, tablespace_name, segment_type
order by owner, segment_type, tablespace_name

*******************************
rdbms ver : 9.2 - 11g
*******************************
keywords: schema size mega
*******************************