Partition name from table

[una]

Hi,

How do find out the partition name where the data from the table is stored.

For example:

select partition_name, column_a from table;

(partition_name is not one of my columns in this table)

Thanks,

Una.

[gandolf989]

I'm not sure what you want.
Does this help?

SELECT partition_name
  FROM user_tab_partitions
 WHERE table_name = 'TABLE';

or

SELECT partition_name
  FROM all_tab_partitions
 WHERE owner = 'OWNER'      AND
       table_name = 'TABLE';

[slimdave]

You can get the partition name from the subobject_name column of the all/user/dba_objects view. The object id for the partition is retrieved by the supplied procedure DBMS_MVIEW.PMARKER().

For example ...

Select
   ao.subobject_name,
   mt.my_column
from
   all_objects ao,
   my_table mt
where
   ao.object_id = DBMS_MVIEW.PMARKER(mt.rowid)
/

[una]

Hi slimdave,

This is exactly what I'm looking for - but for some reason, the object_id returned from the dbms_mview.pmarker(rowid) doesn't exist in the user_objects table.

Any ideas?

Thanks,

Una.

[una]

I figured it out....

the DBMS_MVIEW.PMARKER(rowid) returns the data_object_id from the user_objects table, not the object_id.

Thanks for all your help.

- Una.

[slimdave]

ah hah. okey-dokey then.