206 lines
9.1 KiB
MySQL
206 lines
9.1 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display table partitions and subpartitions
|
|
* Parameters : 1 - OWNER (% - wildchar, \ - escape char)
|
|
* 2 - Table Name (% - wildchar, \ - escape char)
|
|
* 3 - Partition Name (% - wildchar, \ - escape char)
|
|
* 4 - Sub-Partition Name (% - wildchar, \ - escape char)
|
|
* 5 - Object Type (% - wildchar, \ - escape char)
|
|
* 6 - Partition HighValue (% - wildchar, \ - escape char)
|
|
* 7 - Sub-Partition HighValue (% - 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_name
|
|
UNDEFINE subpartition_name
|
|
UNDEFINE object_type
|
|
UNDEFINE part_high_value
|
|
UNDEFINE subpart_high_value
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
DEFINE partition_name="&&3"
|
|
DEFINE subpartition_name="&&4"
|
|
DEFINE object_type="&&5"
|
|
DEFINE part_high_value="&&6"
|
|
DEFINE subpart_high_value="&&7"
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _partition_name NEW_VALUE partition_name NOPRINT
|
|
COLUMN _subpartition_name NEW_VALUE subpartition_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
COLUMN _part_high_value NEW_VALUE part_high_value NOPRINT
|
|
COLUMN _subpart_high_value NEW_VALUE subpart_high_value 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('&&partition_name','','%','&&partition_name') "_partition_name"
|
|
, DECODE('&&subpartition_name','','%','&&subpartition_name') "_subpartition_name"
|
|
, DECODE('&&object_type','','%','&&object_type') "_object_type"
|
|
, DECODE('&&part_high_value','','%','&&part_high_value') "_part_high_value"
|
|
, DECODE('&&subpart_high_value','','%','&&subpart_high_value') "_subpart_high_value"
|
|
FROM DUAL
|
|
;
|
|
|
|
set term on
|
|
|
|
|
|
/***********************************/
|
|
|
|
PROMPT *****************************************************
|
|
PROMPT * Table Partitions/Sub-Partitions
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Table Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
PROMPT * - Partition Name = '&&partition_name'
|
|
PROMPT * - SubPartition Name = '&&subpartition_name'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT * - Part HighValue = '&&part_high_value'
|
|
PROMPT * - SubPart HighValue = '&&subpart_high_value'
|
|
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
|
|
, pt.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
|
|
, GREATEST(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 '\'
|
|
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
|
|
;
|
|
|
|
COLUMN partition_name HEADING "Table Name" FORMAT a40
|
|
COLUMN partition_name HEADING "Partition Name" FORMAT a20
|
|
COLUMN part_high_value HEADING "Partition HighValue" FORMAT a85
|
|
COLUMN subpartition_name HEADING "SubPartition Name" FORMAT a30
|
|
COLUMN subpart_high_value HEADING "SubPartition HighValue" FORMAT a40
|
|
COLUMN partition_position NOPRINT
|
|
COLUMN subpartition_position NOPRINT
|
|
|
|
select DISTINCT
|
|
table_owner
|
|
|| '.' || table_name table_name
|
|
, partition_name
|
|
, partition_position
|
|
, part_high_value
|
|
, subpartition_count
|
|
, CASE '&&object_type'
|
|
WHEN '%' THEN subpartition_name
|
|
WHEN 'TABLE SUBPARTITION' THEN subpartition_name
|
|
ELSE ' '
|
|
END subpartition_name
|
|
, subpartition_position
|
|
, CASE '&&object_type'
|
|
WHEN '%' THEN subpart_high_value
|
|
WHEN 'TABLE SUBPARTITION' THEN subpart_high_value
|
|
ELSE ' '
|
|
END subpart_high_value
|
|
from
|
|
xmltable( '/ROWSET/ROW'
|
|
passing dbms_xmlgen.getXMLType('
|
|
select p.table_owner
|
|
, p.table_name
|
|
, p.partition_name
|
|
, p.partition_position
|
|
, p.high_value part_high_value /* <---- Long Column type*/
|
|
, p.subpartition_count
|
|
, s.subpartition_name
|
|
, s.subpartition_position
|
|
, s.high_value subpart_high_value /* <---- Long Column type*/
|
|
from dba_tab_partitions p
|
|
LEFT OUTER JOIN dba_tab_subpartitions s
|
|
ON s.table_owner = p.table_owner
|
|
AND s.table_name = p.table_name
|
|
AND s.partition_name = p.partition_name
|
|
WHERE p.table_owner LIKE ''' || '&&owner' || ''' ESCAPE ''\''
|
|
AND p.table_name LIKE ''' || '&&table_name' || ''' ESCAPE ''\''
|
|
AND p.partition_name LIKE ''' || '&&partition_name' || ''' ESCAPE ''\''
|
|
AND NVL(s.subpartition_name,''x'') LIKE ''' || '&&subpartition_name' || ''' ESCAPE ''\''
|
|
'
|
|
)
|
|
columns
|
|
table_owner VARCHAR2(30)
|
|
, table_name VARCHAR2(30)
|
|
, partition_name VARCHAR2(30)
|
|
, partition_position NUMBER
|
|
, part_high_value VARCHAR2(4000)
|
|
, subpartition_count NUMBER
|
|
, subpartition_name VARCHAR2(30)
|
|
, subpartition_position NUMBER
|
|
, subpart_high_value VARCHAR2(4000)
|
|
)
|
|
WHERE 1=1
|
|
AND part_high_value LIKE '&&part_high_value' ESCAPE '\'
|
|
--AND NVL(subpart_high_value,'x') LIKE '&&subpart_high_value' ESCAPE '\'
|
|
ORDER BY table_name
|
|
, partition_position
|
|
, subpartition_position
|
|
;
|
|
|
|
|
|
@@footer
|