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
Best Answer
A much faster, way cooler, but also much more complex way would be to use partition exchange.
I would use it only if
Given a mockup with minimal tables, data and columns:
First, you create holding tables to park the data:
Next, you'll swap out the data, children first, then parents:
Then you'll recreate the new table structure:
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:
After the parent is done, the children can be swapped back in:
A little bit of clean up afterwards: