oracle 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 );
