时间戳

首页 > 解决方案 > 查找所有子分区名称

oracle - 查找所有子分区名称

问题描述

该表根据列（实例）进行了子分区，其值可以是“1”或“2”。想要列出所有包含 INSTANCE 值的子分区名称为 '2'

select * from user_tab_subpartitions sp where table_name='TEST' and sp.NUM_ROWS >0
and to_char(HIGH_VALUE)='2';

错误

ORA-00932: inconsistent datatypes: expected CHAR got LONG

HIGH_VALUE 是 LONG() 类型

标签： oraclepartitioninglong-integersubpartition

解决方案

set serveroutput on;

DECLARE
 high_val VARCHAR2(32767); 
BEGIN
FOR rec IN (select PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE  from user_tab_subpartitions sp where table_name='TEST' and sp.NUM_ROWS >0) LOOP
  high_val := rec.HIGH_VALUE;
  IF high_val LIKE '2%' THEN 
    dbms_output.put_line(rec.SUBPARTITION_NAME);
  END IF;
END LOOP;
END;

推荐阅读