125 lines
5.1 KiB
SQL
125 lines
5.1 KiB
SQL
@@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
|