@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display table partitions summary
*  Parameters : 1 - OWNER              (% - wildchar, \ - escape char)
*               2 - Table Name         (% - wildchar, \ - escape char) 
*               3 - Partition Type     (% - wildchar, \ - escape char) 
*               4 - Sub-Partition Type (% - wildchar, \ - escape char) 
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  23-Dec-16  Vishal Gupta  Increased column size of partition_type
*  04-Jun-13  Vishal Gupta  Created
*
*
*/

/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE owner
UNDEFINE table_name
UNDEFINE partition_type
UNDEFINE subpartition_type

DEFINE owner="&&1"
DEFINE table_name="&&2"
DEFINE partition_type="&&3"
DEFINE subpartition_type="&&4"

COLUMN  _owner               NEW_VALUE  owner              NOPRINT
COLUMN  _table_name          NEW_VALUE  table_name         NOPRINT
COLUMN  _partition_type      NEW_VALUE  partition_type     NOPRINT
COLUMN  _subpartition_type   NEW_VALUE  subpartition_type  NOPRINT

set term off
SELECT CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1) 
           ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner')) 
       END                                                                      "_owner"
     , CASE 
           WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1) 
           ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name')) 
       END                                                                      "_table_name"
     , DECODE(UPPER('&&partition_type'),'','%',UPPER('&&partition_type'))       "_partition_type"
     , DECODE(UPPER('&&subpartition_type'),'','%',UPPER('&&subpartition_type')) "_subpartition_type"
FROM DUAL;
set term on
/***********************************/


/************************************
*  CONFIGURATION PARAMETERS
************************************/

/***********************************/

PROMPT *****************************************************
PROMPT * Table Partitions/Sub-Partitions
PROMPT * 
PROMPT * Input Parameters 
PROMPT *    - Owner              = '&&owner'
PROMPT *    - Table Name         = '&&table_name'
PROMPT *    - Partition Type     = '&&partition_type'
PROMPT *    - SubPartition Type  = '&&subpartition_type'
PROMPT *****************************************************

COLUMN table_name                 HEADING "Table Name"                         FORMAT a40
COLUMN partitioning_type          HEADING "Part|Type"                          FORMAT a9
COLUMN partitioning_key_count     HEADING "Part|Key|Count"                     FORMAT 99999
COLUMN interval                   HEADING "Interval"                           FORMAT a30
COLUMN subpartitioning_type       HEADING "SubPart|Type"                       FORMAT a10
COLUMN subpartitioning_key_count  HEADING "SubPart|Key|Count"                  FORMAT 99999
COLUMN def_subpartition_count     HEADING "Default|Subpart|Count|PerPart"      FORMAT 999
COLUMN status                     HEADING "Status"                             FORMAT a8
COLUMN partition_count            HEADING "Part|Count"                         FORMAT 999,999
COLUMN subpartition_count         HEADING "SubPart|Count"                      FORMAT 999,999
COLUMN total_count                HEADING "Total|Count"                        FORMAT 999,999



SELECT pt.owner  || '.' || pt.table_name table_name
     , pt.status
     , NVL2(pt.interval,'INTERVAL',pt.partitioning_type) partitioning_type
     , pt.partitioning_key_count
     -- for interval RANGE partition, dba_part_tables.partition_count is always 1048575
     -- , so we need to take actual partition count from dba_tab_partitions
     , count(distinct p.partition_name) partition_count
     , pt.interval
     , pt.subpartitioning_type
     , pt.subpartitioning_key_count
     , pt.def_subpartition_count
     , count(s.subpartition_name) subpartition_count
     , count(distinct p.partition_name) + count(s.subpartition_name) total_count
  FROM dba_part_tables pt
       JOIN dba_tab_partitions p ON p.table_owner = pt.owner AND p.table_name = pt.table_name
       LEFT OUTER JOIN dba_tab_subpartitions s ON s.table_owner = pt.owner AND s.table_name  = pt.table_name AND p.partition_name = s.partition_name
WHERE 1=1
  AND pt.owner                LIKE '&&owner'             ESCAPE '\'
  AND pt.table_name           LIKE '&&table_name'        ESCAPE '\'
  AND pt.partitioning_type    LIKE '&&partition_type'    ESCAPE '\'
  AND pt.subpartitioning_type LIKE '&&subpartition_type' ESCAPE '\'
GROUP BY        
       pt.owner 
     , pt.table_name
     , pt.partitioning_type
     , pt.interval
     , pt.subpartitioning_type
     , pt.def_subpartition_count
     , pt.partitioning_key_count
     , pt.subpartitioning_key_count
     , pt.status
--ORDER BY total_count desc    
ORDER BY pt.owner
       , pt.table_name 
;   


@@footer