oracle get partition name by high value
-- Sortable (by high_value) list of partitions / subpartitions in a table
-- For partitions, change ALL_TAB_SUBPARTITIONS to ALL_TAB_PARTITIONS
SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE FROM (
WITH xml_s AS (
SELECT dbms_xmlgen.getxmltype('SELECT TABLE_NAME,PARTITION_NAME,
SUBPARTITION_NAME, HIGH_VALUE
FROM ALL_TAB_SUBPARTITIONS WHERE TABLE_NAME=''MY_TABLE''
AND TABLE_OWNER=''MY_USER''') as x
FROM dual
)
SELECT xmltab.* FROM
xml_s,
xmltable(
'/ROWSET/ROW' passing xml_s.x columns
TABLE_NAME varchar2(30) path 'TABLE_NAME',
PARTITION_NAME varchar2(30) path 'PARTITION_NAME',
SUBPARTITION_NAME varchar2(30) path 'SUBPARTITION_NAME',
HIGH_VALUE varchar2(200) path 'HIGH_VALUE'
) xmltab
ORDER BY TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE
);