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_tab_ext.sql
REM
REM <SQLDIR_GRP>MOST SEGMENT</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Lists tables/Indexes with > 50 extents
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tab_ext.sql
REM 
REM Example:
REM	s_tab_ext.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

@@x_db_name.sql
set verify off

prompt Lists tables/Indexes with > 50 extents
accept sgtype char prompt "List TABLES-[1] or INDEXES-[2] :"

set lines 132
set trims on
set pages 60
col owner           format a10
col segment_name    format a37
col Kbytes_free     format 9999999 heading "KB|Free"
col largest         format 9999999 heading "KB|Largest"
col KB_Initial      format 999999  heading "KB|Init"
col KB_next         format 999999  heading "KB|Next"
col extents         format 999999  heading "NO|Ext"
col max_extents     format 999999  heading "MAX|Ext"
col pct_increase    format 999 heading "%|Inc"
col pct_increase    format 999 heading "%|Inc"
col pct_increase    format 999 heading "%|Inc"
col tablespace_name format a10 trunc heading "TS Name"
col segment_type    format a7  trunc heading "Seg Type"

SELECT /*+ ORDERED */
       sg.segment_type
,      sg.tablespace_name
,      fr.Kbytes_free
,      fr.largest
,      sg.owner||'.'||sg.segment_name segment_name
,      trunc(sg.bytes/1024/1024) mbytes
,      sg.extents
,      sg.max_extents
,      sg.initial_extent/1024 KB_Initial
,      sg.next_extent/1024 KB_next
,      sg.pct_increase
FROM dba_segments sg
,    (select	sum(bytes)/1024	Kbytes_free
      ,		max(bytes)/1024	largest
      ,		tablespace_name
      from	dba_free_space
      group by tablespace_name)	 fr
WHERE sg.segment_type LIKE DECODE('&sgtype','1','TABLE%','2','INDEX%')
AND   INSTR(sg.owner,'SYS') < 1
AND   sg.extents > 50
AND   sg.tablespace_name = fr.tablespace_name(+)
ORDER BY extents DESC


spool s_tab_ext.&db_name..&sgtype..&tstamp..log
/
spool off