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_figure_subparts.sql
REM
REM <SQLDIR_GRP>PART TAB</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(www.dbatoolz.com)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	I use this script on tables that are not yet
REM	partitioned but need to be partitioned.
REM	It allows me to figure out number of subpartitions.
REM	--------
REM	STRATEGY:
REM 	+------------------------------------------+
REM 	Goal:
REM 	        20 EXTENTS per SUBPARTITION
REM 	Rule:   
REM 	        2sp     for 0-40        EXTENT TABLE
REM 	        8sp     for 41-160      EXTENT TABLE
REM 	        16sp    for 161+        EXTENT TABLE
REM 	+------------------------------------------+
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tab_figure_subparts.sql
REM 
REM Example:
REM	s_tab_figure_subparts.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

set pages 66
set lines 132
set trims on
set verify off

col sub_part format 99 heading "Sp"
accept owner prompt "Enter value for object owner: "


col seg_type format a15
col segment_name format a35

select owner||'.'||substr(SEGMENT_TYPE,1,10) seg_type
,      SEGMENT_NAME
,      '16' sub_part
,      EXTENTS
,      ROUND(EXTENTS/16) ext_per_sub
,      bytes/1024/1024 mbytes
,      bytes/1024 kbytes
from   dba_segments
where  owner='&&owner'
and    segment_name like '&&tab_name_like'
and    EXTENTS > 160
and    segment_type not like 'INDEX%'
UNION ALL
select owner||'.'||substr(SEGMENT_TYPE,1,10) seg_type
,      SEGMENT_NAME
,      '8' sub_part
,      EXTENTS
,      ROUND(EXTENTS/8) ext_per_sub
,      bytes/1024/1024 mbytes
,      bytes/1024 kbytes
from   dba_segments
where  owner='&&owner'
and    segment_name like '&&tab_name_like'
and    (EXTENTS <= 160 and EXTENTS > 40)
and    segment_type not like 'INDEX%'
UNION ALL
select owner||'.'||substr(SEGMENT_TYPE,1,10) seg_type
,      SEGMENT_NAME
,      '2' sub_part
,      EXTENTS
,      ROUND(EXTENTS/2) ext_per_sub
,      bytes/1024/1024 mbytes
,      bytes/1024 kbytes
from   dba_segments
where  owner='&&owner'
and    segment_name like '&&tab_name_like'
and    EXTENTS <= 40
and    segment_type not like 'INDEX%'
order by 1
,        2

spool SEGS_size_&&owner..&&tab_name_like..log
/
/*
+------------------------------------------+
Goal:
        20 EXTENTS per SUBPARTITION
Rule:   
        2sp     for 0-40        EXTENT TABLE
        8sp     for 41-160      EXTENT TABLE
        16sp    for 161+        EXTENT TABLE
+------------------------------------------+
*/
spool off


undefine owner