Partition name from table

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.

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

Code:

---

SELECT partition_name
  FROM user_tab_partitions
 WHERE table_name = 'TABLE';

---

or

Code:

---

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

---

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 ...

Code:

---

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

---

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.

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.

ah hah. okey-dokey then.