5

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

bumped to the homepage by Community yesterday

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

migrated from stackoverflow.com Jun 1 '18 at 21:57

This question came from our site for professional and enthusiast programmers.

  • "cannot drop the tables and lose the data": is a downtime ok and do you have enough space to keep two copies of the data for a short period of time? – wolφi Jun 1 '18 at 10:51
  • yes I do..I was reading about the cloning technique but was just wondering if it can be done using simple ALTER commands. – karishma nagelia Jun 1 '18 at 11:11
  • 1
    That's good. I am not sure if a partitioning method can be changed. I will follow your question :-). Can you edit the post and show us a minimal table structure of one parent and one or two children (may be not with all the columns, just a couple), ideally as CREATE statements? – wolφi Jun 1 '18 at 11:17
  • 1
    @WilliamRobertson: I was having thoughts along those lines, therefore asked for the table structure to show an example. Thanks for clarifying that the partitioning type cannot be changed. – wolφi Jun 1 '18 at 19:28
  • 1
    To those proposing to close the question: karishma's question is clearly formulated, relevant to my job, the answer is not easy and will possibly be interesting to other users... – wolφi Jun 1 '18 at 19:31
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;
  • I actually checked the data in the database, the record count is quite low,close to 40.I am thinking about taking a dump of data as INSERT statements and then dumping the data in the newly created tables.It looks simpler as the data count is less.Do you see any challenges? – karishma nagelia Jun 4 '18 at 8:09
  • No, not at all, that is actually the best solution in your case, good thinking! – wolφi Jun 4 '18 at 8:19
0

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.

0

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
  • Is there any particular reason to create indexes and constraints after copying the data? can it be done in step 2 itself? – karishma nagelia Jun 4 '18 at 8:13
  • It is faster to insert into a table and create the index afterwards. – wolφi Jun 4 '18 at 8:15
  • Just saw your other comment about the rowcount. With 40 rows it doesn't matter, you can do it in step 2 itself. – wolφi Jun 4 '18 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.