REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_user_obj.sql
REM
REM <SQLDIR_GRP>TABSP USER SEGMENT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports counts of segments per user/tablespace
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_user_obj.sql
REM 
REM Example:
REM	s_user_obj.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

ttitle off
clear col
clear breaks
clear computes

set pages 1000
SET HEADING ON
SET ECHO OFF
set feedback off

col owner             format a17
col tablespace_name   format a15
col size_MB           format 9999999.99
break on owner skip 1
compute sum of size_MB on owner

accept owner prompt "Enter Owner Name [Enter For All]:"




select  owner
,	count(*) count
,       segment_type
,	tablespace_name
,       sum(bytes/1024/1024)   size_MB
from	dba_segments
where	owner = nvl(upper('&owner'),owner)
group by   segment_type
,	   tablespace_name
,          owner
order by owner
,        segment_type
,	 tablespace_name
,        count
,        size_MB
/