How to get the name of the Partition within a table that a ROWID comes from...

Oracle's partitioning feature is extremely handy for allowing you to break up a huge table into smaller, more manageable pieces.

I use it extensively for Datawarehousing - where it allows one to add and remove fact data partitioned by time/date very efficiently.

What is hard, however - is to find out which partition a particular row comes from within a table. Well - not any more. Just create this handy little function to get the name of the partition that any row comes from (using the "ROWID" pseudocolumn).

Here goes:

CREATE OR REPLACE FUNCTION get_partition_name_for_rowid (
   p_rowid   IN   ROWID)
   RETURN VARCHAR2 AS
   -- Variables
   l_all_objects_rec                  all_objects%ROWTYPE;
   l_partition_name                   all_tab_partitions.partition_name%TYPE;
   -- Custom Exceptions
   rowid_not_from_a_partition         EXCEPTION;
BEGIN
   SELECT /*+ FIRST_ROWS */
          *
     INTO l_all_objects_rec
     FROM all_objects
    WHERE data_object_id = dbms_mview.pmarker (p_rowid);

   IF l_all_objects_rec.object_type = 'TABLE PARTITION' THEN
      l_partition_name := l_all_objects_rec.subobject_name;
   ELSE
      RAISE rowid_not_from_a_partition;
   END IF;

   RETURN l_partition_name;
EXCEPTION
   WHEN rowid_not_from_a_partition THEN
      raise_application_error (-20101
                             , build_string (p_input_string      => 'ROWID: %s is in table: %s - which is not partitioned.'
                                           , str1                => p_rowid
                                           , str2                => l_all_objects_rec.object_name));
   WHEN OTHERS THEN
      RAISE;
END get_partition_name_for_rowid;
/

Just run this SQL to test it:

CREATE TABLE part_table
  (partition_key INTEGER
 , some_other_column VARCHAR2 (30)
  )
PARTITION BY RANGE (partition_key)
 ( PARTITION p1 VALUES LESS THAN (50)
 , PARTITION p2 VALUES LESS THAN (MAXVALUE)
 )
;

INSERT INTO part_table
(partition_key, some_other_column)
SELECT ROWNUM
     , TO_CHAR (ROWNUM)
  FROM all_objects
 WHERE ROWNUM <= 100;
 
SELECT get_partition_name_for_rowid (ROWID) AS partition_name
     , partition_key
     , some_other_column
  FROM part_table
 WHERE partition_key BETWEEN 47 AND 52;

Semper Fidelis