Get Partition Name By Its Value

There are many business cases where you need to identify in which partition is a particular value located. This query will help you to do so.

-- this particular example extracts date... feel free to modify it according to your needs
WITH w_data AS
 (SELECT table_name
        ,to_date(TRIM('''' FROM
                                                       dbms_xmlgen.getxmltype('SELECT high_value 
                                                                         FROM dba_tab_partitions 
                                                                         WHERE 1=1
                                                                         AND table_name='''||table_name ||'''
                                                                         AND table_owner = '''||table_owner ||''' 
                                                                         AND partition_name = '''||partition_name ||'''
                                                       , '//text()'
                                            , '''.*?'''
                   , 'SYYYY-MM-DD HH24:MI:SS'
                  ) high_value_in_date_format
  FROM dba_tab_partitions
  WHERE table_name = 'SAMPLE_TABLE'
  AND table_owner = 'SAMPLE_OWNER')

SELECT table_name
      ,lead(high_value_in_date_format) over(PARTITION BY table_name ORDER BY partition_name DESC) as value_from
      ,high_value_in_date_format - 1 as value_to
FROM w_data