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_tabsp_top_10_segs.sql
REM
REM <SQLDIR_GRP>TABSP MOST SEGMENT STORAGE</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Displays top 10 biggest segments by tablespace
REM	WARNING:
REM	Uses ORDER BY with INLINE VIEWS not supported for <8i
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tabsp_top_10_segs.sql <TABLESPACE_NAME>
REM 
REM Example:
REM	To display top 10 biggest segments
REM	in SYSTEM tablespace:
REM	
REM	s_tabsp_top_10_segs.sql SYSTEM
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

col owner format a15
col segment_name format a35

select * from
(
select owner, segment_Type, segment_name, bytes/1024 kbytes
from dba_segments
where tablespace_name = '&ts_name'
order by bytes/1024 desc
)
where rownum < 10
/