switching from normal list partitioning to reference partitioning in oracle 11g

Question

I have a main parent table and multiple child tables which have normal partitioning in it, wherein the partition column is repeated in all child tables. I want to switch to reference partition(oracle 11g). My child tables currently have 2 partitions each, both of which I need to drop and replace with reference partition.

Currently I am facing issues with dropping the last partition and adding the new reference partition using ALTER commands.

Is there a way to do this as I cannot drop the tables and lose the data There are indexes created on the partition column.

The queries I am using:

ALTER TABLE CHILD_TABLE_1
    DROP PARTITIONS part1, part2
    UPDATE INDEXES; --- here only one partition was dropped

ALTER TABLE CHILD_TABLE_1 ADD PARTITION BY REFERENCE (foreign_key_with_parent);

this gives error

Error: ORA-00902: invalid datatype SQLState: 42000 ErrorCode: 902

Answer 1

A much faster, way cooler, but also much more complex way would be to use partition exchange.

I would use it only if

• there is enough data (say > 1 million rows, or partitions > 1 GB)
• you have a test database/schema with identical structure and sufficient amounts of data, which can be easily recreated (in my experience, the scripts usually work only correct after the second try)
• you have the time to develope and test the migration script

Given a mockup with minimal tables, data and columns:

CREATE TABLE parent(id NUMBER PRIMARY KEY, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9), 
  PARTITION p2 VALUES(0,2,4,6,8));

CREATE TABLE child(id REFERENCES parent, par NUMBER, t VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9), 
  PARTITION p2 VALUES(0,2,4,6,8));      

INSERT INTO parent(id,par,c)
SELECT object_id, mod(object_id,10) as par, object_name FROM all_objects;

INSERT INTO child(id,par,t)   
SELECT object_id, mod(object_id,10) as par, object_type FROM all_objects;

First, you create holding tables to park the data:

CREATE TABLE temp_parent_p1 AS SELECT * FROM parent WHERE 1=0;
CREATE TABLE temp_parent_p2 AS SELECT * FROM parent WHERE 1=0;
CREATE TABLE temp_child_p1  AS SELECT * FROM child  WHERE 1=0;
CREATE TABLE temp_child_p2  AS SELECT * FROM child  WHERE 1=0;

Next, you'll swap out the data, children first, then parents:

ALTER TABLE child  EXCHANGE PARTITION p1 WITH TABLE temp_child_p1;
ALTER TABLE child  EXCHANGE PARTITION p2 WITH TABLE temp_child_p2;
DROP  TABLE child;
ALTER TABLE parent EXCHANGE PARTITION p1 WITH TABLE temp_parent_p1;
ALTER TABLE parent EXCHANGE PARTITION p2 WITH TABLE temp_parent_p2;
DROP  TABLE parent;

Then you'll recreate the new table structure:

CREATE TABLE parent(id NUMBER PRIMARY KEY, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9), 
  PARTITION p2 VALUES(0,2,4,6,8));

CREATE TABLE child(id REFERENCES parent, par NUMBER, c VARCHAR2(30)
) PARTITION BY LIST (par) (
  PARTITION p1 VALUES(1,3,5,7,9), 
  PARTITION p2 VALUES(0,2,4,6,8));

Now the parked partitions need to be indexed. The columns and constraints need to have exactly the same structure as the new tables (but don't worry about the names). Then you can swap the data back in:

ALTER TABLE temp_parent_p1 ADD PRIMARY KEY (id);
ALTER TABLE temp_parent_p2 ADD PRIMARY KEY (id);
ALTER TABLE parent EXCHANGE PARTITION p1 WITH TABLE temp_parent_p1;
ALTER TABLE parent EXCHANGE PARTITION p2 WITH TABLE temp_parent_p2;

After the parent is done, the children can be swapped back in:

ALTER TABLE temp_child_p1 ADD FOREIGN KEY (id) REFERENCES parent(id);
ALTER TABLE temp_child_p2 ADD FOREIGN KEY (id) REFERENCES parent(id);
ALTER TABLE child  EXCHANGE PARTITION p1 WITH TABLE temp_child_p1;
ALTER TABLE child  EXCHANGE PARTITION p2 WITH TABLE temp_child_p2;

A little bit of clean up afterwards:

BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS(NULL,'parent');
  DBMS_STATS.GATHER_TABLE_STATS(NULL,'child');
END;
/
DROP TABLE temp_parent_p1;
DROP TABLE temp_parent_p2;
DROP TABLE temp_child_p1;
DROP TABLE temp_child_p2;

Answer 2

I had asked about converting from Range Partition to Reference in a different forum.

https://community.oracle.com/message/14807741

The answer is: DBMS_REDEFINITION

This would be your best option since you have the extra space and at least some constraints (fk) that needs to be migrated to the new table.

DBMS_REDEFINITION does all the heavy lifting (move constraints) for you.

Answer 3

The traditional approach would be

1. Rename old child and parent tables
2. Create new tables with partition by reference (including primary key on parent table and foreign key on child tables as this is required by partition by reference)
3. Copy data from old to new tables
4. Create other indexes and constraints
5. Gather statistics on the new tables
6. Drop old tables