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_part.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 partitioned tables from dba_part_tables
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tab_part.sql
REM 
REM Example:
REM	s_tab_part.sql
REM
REM
REM History:
REM	11-05-2001	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 columns
clear computes
ttitle "Table Partitions"

set trims on
set pages 80
set verify off
set lines 80
--col table_name  format a30 trunc heading "Owner.Table"
col table_name  format a35 heading "Owner.Table"


spool s_tab_part.log

select owner||'.'||table_name table_name
, PARTITIONING_TYPE
, SUBPARTITIONING_TYPE 
, PARTITION_COUNT
FROM dba_part_tables
WHERE DECODE(UPPER('&&1'),NULL,'x',owner) = NVL(UPPER('&&1'),'x')
AND   DECODE(UPPER('&&2'),NULL,'x',table_name) = NVL(UPPER('&&2'),'x')
ORDER BY 1,2
/

set lines 300
col high_value format a10
col composite format a3 heading "CMP"
col partition_position format 999 heading "Pos"
col subpartition_count format 999 heading "Cnt"
col init_kb format 999999 heading "Init KB"
col next_kb format 999999 heading "Next KB"
col num_rows format 9999999 heading "Num Rows"
col pct_increase format 999 heading "Pct|Inc"
col partition_name format a30 heading "Part Name"
col tablespace_name format a10 heading "Tabsp Name"
break on table_name skip 1

SELECT
 table_owner||'.'||table_name table_name
,composite
,partition_name
,subpartition_count
,high_value
,partition_position
,tablespace_name
,initial_extent/1024 init_kb
,next_extent/1024 next_kb
,pct_increase
,num_rows
,last_analyzed
FROM dba_tab_partitions
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,3
/

spool off

undefine 1
undefine 2