0

This forum has helped me earlier on many occasions and now I need someone to help me with this.

I am looking to convert a pre-existing partitioned (range) table - to sub-partitioned (composite range-list).

1) I Range Partitioned an existing table using the query below:

alter table PART_TEST 
modify PARTITION BY RANGE (CREATEDATE)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('15-MAY-2019', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('16-MAY-2019', 'DD-MON-YYYY')),
PARTITION p3 VALUES LESS THAN (maxvalue)) online;

2) Then I tried to alter the table to add sub-partitions (list) using the query below:

ALTER TABLE PART_TEST
MODIFY PARTITION P1 
ADD SUBPARTITION SP1
VALUES ('1') tablespace TEST_PART;

But I see this error:

Error report - ORA-14253: table is not partitioned by composite partition method 14253. 00000 - "table is not partitioned by Composite Range method" *Cause: The table in a subpartition maintenance operation (ALTER TABLE EXCHANGE/MODIFY/MOVE/TRUNCATE SUBPARTITION, or ALTER TABLE MODIFY PARTITION ADD/COALESCE SUBPARTITION command must be partitioned by Composite Range method *Action: Ensure that the table is partitioned by Composite Range method

Below is what I am tasked with,

Scenario - 1: ==> I want to have my existing partitions(range) subpartitioned (list) so that my old data is usable. The data in each of my partitions have to be moved to a sub-partition,

Scenario - 2: Then revert back to partition level (reverse migrate), ==> I mean the data from all of the sub-partitions within each partition is moved to be at the partition level and no sub-partitions are left in the database.

Is it possible and is there a way to do so? If so how to implement the above scenarios - convert existing partitions(range) to sub-partitioned (Range-list) - and how to move back the data from sub-partitions to each of its partition back and no-sub partitions are left.

  • You can do it with DBMS_REDEFINITION. There's an answer on here somewhere that I wrote that details steps – Philᵀᴹ May 21 at 9:06
  • i didn't find the answer to my question among the 3 of your replies related to DBMS_REDEFINITION – kevin May 21 at 11:32
  • it would really help if you could help me with the steps. – kevin May 21 at 12:06
  • there won't be an answer already that is 100% what you need to do. It's a lot of work to answer this in detail, I can't really spend an hour answering the question for you. – Philᵀᴹ May 21 at 13:16
  • Not clear with requirements and why do go all the trouble to revert back?.Check this link Move Partition .Hope that gives an idea – Sam May 21 at 17:40
1

Scenario 1 - redefine the table

Your SQL is adding a sub-partition, not redefining the table. I suspect you need to redefine the table to have sub-partitions before you can add a sub-partition.

12.2c+

The ability to change a non-partitioned table into a partitioned table via ALTER TABLE was introduced in 12.2c.

Looking at the documentation, it appears that you can change a RANGE partitioned table into a RANGE-LIST partition in the same way. I haven't tried; be cautious; use DEV for practice.

pre-12.2c

DBMS_REDEFINITION is the de facto way of changing a table between non-partitioned and partition designs.

Google is your friend. But, here is my recommended link:

Scenario 2 - convert back to non-partitioned table.

This requirement perplexes me. Doing such a massive change, multiple times, is probably a Tell-Tale sign that you have a more serious problem that you should be solving.

Any way, I suspect that the only way to do this is with DBMS_REDEFINITION.

  • Thanks for the pointers and useful information. I could figure out the fix for this error but I do not see any data in the table I used for exchange. The error we saw earlier was due to - option 3 mentioned in - ORA-14097 At Exchange Partition After Adding Column With Default Value (Doc ID 1334763.1). [ i was adding a purgeid column post IMAGE_RANGE table and I followed the same as mentioned in this doc for the rest of 2 tables] its difficult to follow DMBS_REDEFINITION. – kevin May 23 at 10:25
  • These below work without any issue, but alter table IMAGE_RANGE exchange partition p1 with table IMAGE_T excluding indexes without validation; alter table IMAGE_RANGE_LIST exchange subpartition p1_s1 with table IMAGE_T excluding indexes without validation; but I do not see any partitions nor data when I query the table I created for exchange, SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM user_tab_partitions WHERE TABLE_NAME = 'IMAGE_T'; SELECT PARTITION_NAME,SUBPARTITION_NAME, HIGH_VALUE FROM ALL_TAB_SUBPARTITIONS WHERE TABLE_OWNER = 'SCHEMA_NAME' AND TABLE_NAME = 'IMAGE_T'; – kevin May 23 at 10:27

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.