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;