how to find partition of record oracle

database, database-partitioning, oracle, oracle11g, plsql

Solution

Select the rowid for the row, and the `DBMS_RowID.RowID_Object()` procedure will extract the data object id.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_rowid.htm#i997153

Look up that data object id in the data dictionary and read the subobject_name.

For example:

   SELECT dbms_rowid.rowid_object(ROWID) data_object_id
   FROM INVOICE
   WHERE INVOICE_ID = 2268041139:

   -- data_object_id = 546512

   select * from user_objects where data_object_id = 546512;

   -- SUBOBJECT_NAME = 'PART_P2099_P00'     
   -- OBJECT_TYPE = TABLE PARTITION
   -- OBJECT_ID = 464826
   -- DATA_OBJECT_ID = 546512

Problem

I have a table and there is a partition on it. There are 16 hash partition which is starting from SUBSCRIBER_01 .. etc Table name: SUBSCRIBER Partition Column: CUSTOMER_ID (VARCHAR2 10) Database : 11g How can I find partition of a record? Like Customer_ID=933587

Original source