查找所有小区名称

Question

该表是基于列（instance）的列的分区，谁的值可以为'1'或'2'。想要列出所有包含实例值为'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属于type long（）

Answer 1

您可以获取所有带有该值的记录的特定值的子分区。

尝试此查询：

select distinct subobject_name
from user_objects obj join TEST tbl on obj.object_name = 'TEST' 
 and dbms_rowid.rowid_object(tbl.rowid) = obj.data_object_id 
where tbl.INSTANCE = 2;

Answer 2

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;