oracle get partition name by high value code example

Example: 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
);