Oracle查询user_tab_partitions按high_value排序报ORA-00997错误如何解决

报错根因 #

user_tab_partitions视图中的high_value字段为LONG数据类型，Oracle原生不支持对LONG类型直接执行ORDER BY排序、等值匹配等操作，因此直接排序会触发ORA-00997: illegal use of LONG datatype报错。

解决方案 #

方案1：使用内置排序字段partition_position（最优，推荐） #

Oracle官方已在user_tab_partitions视图内置partition_position字段，该字段值默认按照分区的high_value边界从小到大生成序号，无需处理LONG类型即可实现和按high_value排序完全一致的效果，性能无损耗：

SELECT partition_name, high_value 
FROM user_tab_partitions 
WHERE table_name = 'BRD_JOB_DETAILS_TMP' 
ORDER BY partition_position ASC;

按该顺序处理分区，新增分区时保证新分区边界大于最后一个已存在分区的边界，即可完全规避ORA-14074: partition bound must collate higher than that of the last partition报错。

方案2：转换LONG为CLOB后排序（适用于需要判断high_value内容的场景） #

如果业务逻辑需要读取high_value的具体值做判断，可以通过中转转换处理LONG类型：

方法A：临时表中转

-- 1. 建临时表将LONG转换为CLOB
CREATE GLOBAL TEMPORARY TABLE tmp_partition_info ON COMMIT DELETE ROWS AS
SELECT partition_name, TO_LOB(high_value) AS high_value_clob
FROM user_tab_partitions
WHERE table_name = 'BRD_JOB_DETAILS_TMP';

-- 2. 按转换后的CLOB字段排序
SELECT partition_name, high_value_clob
FROM tmp_partition_info
ORDER BY high_value_clob ASC;

方法B：PL/SQL块内直接遍历

如果你是在PL/SQL逻辑中处理分区，直接按partition_position排序遍历即可，PL/SQL支持直接读取LONG类型的值使用：

DECLARE
    v_target_table VARCHAR2(128) := 'BRD_JOB_DETAILS_TMP';
BEGIN
    FOR part_rec IN (
        SELECT partition_name, high_value
        FROM user_tab_partitions
        WHERE table_name = v_target_table
        ORDER BY partition_position ASC
    ) LOOP
        -- 此处按顺序执行分区处理逻辑，part_rec.high_value可直接读取
        DBMS_OUTPUT.PUT_LINE('处理分区：' || part_rec.partition_name || '，分区边界：' || SUBSTR(part_rec.high_value, 1, 200));
    END LOOP;
END;
/

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