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_subpart.sql
REM
REM <SQLDIR_GRP>TAB PART</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports table subpartition details
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tab_subpart.sql
REM 
REM Example:
REM	s_tab_subpart.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

accept 1 prompt "Enter table owner: [Enter for ALL]: "
accept 2 prompt "Enter table name: [Enter for ALL]: "


set feedback off
alter session set nls_date_format='MON-DD hh24:mi:ss';
set feedback on


clear breaks
clear computes
ttitle "Subpartitions"

set trims on
set pages 60
set verify off
set lines 132
col table_name  format a25 trunc heading "Owner.Table"
col part        format a10       heading "Partition"
col subpart     format a24       heading "Tablespace.Subpart"
col num_rows    format 99999999  heading "Num Of Rows"
col blocks      format 99999     heading "Blks"
col eblocks     format 99999     heading "Empty|Blks"
col avg_row_len format 99999     heading "Avg|Row|Len"
col sample_size format 99999     heading "Sample|Size"

break on table_name skip 2
compute sum of num_rows on table_name

  SELECT table_owner||'.'||table_name table_name,
         partition_name part,
         tablespace_name||'.'||subpartition_name subpart,
         num_rows,
         avg_row_len,
         blocks,
         empty_blocks eblocks,
         last_analyzed "Last Analyzed",
         sample_size
    FROM dba_tab_subpartitions
   WHERE DECODE(UPPER('&&1'),NULL,'x',table_owner) = NVL(UPPER('&&1'),'x')
     AND DECODE(UPPER('&&2'),NULL,'x',table_name) = NVL(UPPER('&&2'),'x')
ORDER BY 1,2,subpartition_position

spool s_tab_subpart.log
/
spool off


undefine 1
undefine 2