Oracle中如何根据分区值获取LIST分区表的分区名称？

直接截断目标分区（无需查询分区名） #

如果你的最终目的是截断指定日期对应的分区数据，不需要先查询分区名称，可以直接使用PARTITION FOR子句，指定分区键的值即可：

ALTER TABLE INSURANCE_PAYMENT TRUNCATE PARTITION FOR (TO_DATE('2024-05-29', 'YYYY-MM-DD'));

这个语句会自动匹配到对应日期的分区（不管是静态创建的P1还是自动生成的SYS_开头分区），直接执行截断操作。

若需查询分区名称的解决方法 #

如果确实需要获取对应日期的分区名称，由于ALL_TAB_PARTITIONS.HIGH_VALUE是LONG类型，无法直接在WHERE子句中进行比较，可通过以下两种方式处理：

方法1：自定义函数转换HIGH_VALUE为日期 #

创建一个函数，将LONG类型的HIGH_VALUE转换为日期，再进行比较：

CREATE OR REPLACE FUNCTION get_partition_date(p_table_name IN VARCHAR2, p_partition_name IN VARCHAR2) RETURN DATE IS
    v_high_value LONG;
    v_date DATE;
BEGIN
    SELECT high_value INTO v_high_value
    FROM all_tab_partitions
    WHERE table_name = UPPER(p_table_name)
      AND partition_name = UPPER(p_partition_name);
    
    EXECUTE IMMEDIATE 'SELECT ' || v_high_value || ' FROM DUAL' INTO v_date;
    RETURN v_date;
END;
/

创建完成后，即可用以下查询获取目标分区名称：

SELECT partition_name
FROM all_tab_partitions
WHERE table_name = 'INSURANCE_PAYMENT'
  AND get_partition_date('INSURANCE_PAYMENT', partition_name) = TO_DATE('2024-05-29', 'YYYY-MM-DD');

方法2：通过XML转换LONG为字符串后解析 #

利用DBMS_XMLGEN将LONG类型转换为字符串，再提取日期部分进行匹配：

WITH partition_details AS (
    SELECT partition_name,
           XMLTYPE(DBMS_XMLGEN.GETXML(
               'SELECT high_value FROM all_tab_partitions WHERE table_name=''' || table_name || ''' AND partition_name=''' || partition_name || ''''
           )).EXTRACT('/ROWSET/ROW/HIGH_VALUE/text()').GETSTRINGVAL() AS high_value_str
    FROM all_tab_partitions
    WHERE table_name = 'INSURANCE_PAYMENT'
)
SELECT partition_name
FROM partition_details
WHERE TO_DATE(SUBSTR(high_value_str, INSTR(high_value_str, '''') + 1, 19), 'SYYYY-MM-DD HH24:MI:SS') = TO_DATE('2024-05-29', 'YYYY-MM-DD');

内容的提问来源于stack exchange，提问作者user13906258