In cases where it is difficult or impossible to infer the partition and/or subpartition name logically, (hash partitioning, for example, or with complex multicolumn range/list partitioning) there are a couple of other techniques that you can use. In the DBMS_MVIEW package there is a PMARKER function that returns the data object id for a rownumber, and you can join to user/dba/all_objects using that. Here's a funky example script using variable numbers of hash subpartitions ... drop table t / create table t (col1 , col2 , col3 ) partition by range (col1) subpartition by hash (col2) ( partition p1 values less than (2) subpartitions 2, partition p2 values less than (3) subpartitions 4, partition p3 values less than (4) subpartitions 8 ) as select mod(rownum,3)+1, floor(dbms_random.value(1,256)), floor(dbms_random.value(1,10)) from all_objects where rownum < 101 / with obj as (select --+ materialize data_object_id, subobject_name from user_objects where object_name = 'T' and object_type = 'TABLE SUBPARTITION') select subobject_name, col3 from T, obj where data_object_id = DBMS_MView.PMarker(t.rowid) order by 1 / A second way is to use the TBL$OR$IDX$PART$NUM() function to get the appropriate partition number for a table value or set of values. It's an undocumented (except through metalink, if that counts) function with a couple of magic numbers in it, but the general format to use is ... TBL$OR$IDX$PART$NUM("PARTITIONED_TABLE_NAME", 0, d#, p#, "COLUMN_NAME") The easiest way to get the appropriate format to use for this function is to run a trace on a "with validation" partition exchange against the table of interest, and you'll pretty much get the complete SQL that you need. So you might end up with something similar to ... with utp as (select --+ materialize partition_position, partition_name from user_tab_partitions where table_name = 'MY_TABLE') select utp.partition_name, last_name, first_name from my_table, utp where utp.partition_position = TBL$OR$IDX$PART$NUM("MY_TABLE", 0, 0, 65535, "PART_COL") / I suspect that DBMS_MView.PMarker is easier, although in some circumstances it is slower than TBL$OR$IDX$PART$NUM because the latter is more amenable to reducing the number of function-calls by pre-aggregating the data before applying the function. -- //www.freelists.org/webpage/oracle-l