Find all Subpartition name

Question

The Table was subpartitioned based on column(INSTANCE) who's value can be '1' or '2'. Want to list all subpartition name which contains INSTANCE value as '2'

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

ERROR

ORA-00932: inconsistent datatypes: expected CHAR got LONG

HIGH_VALUE is of type LONG()

Answer 1

You can get all subpartitions containing a specific value by rowids of the records with that value.

Try this query:

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;