Add Sub-partitions

Ken18

I used the below to create a partitioned table ,

alter table Table_name 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;

I tried the something like below,

1)

ALTER TABLE  mytable_name   MODIFY PARTITION P1 ADD SUBPARTITION SP1  VALUES ('0');

Error : Ensure that the table is partitioned by Composite Range method

2)

alter table mytable_namemodify PARTITION BY RANGE (CREATEDATE)SUBPARTITION BY LIST (PID)subpartition template(SUBPARTITION SP1 values (0),SUBPARTITION SP2 values (1),SUBPARTITION SP3 values (2),SUBPARTITION SP4 values (3));

Both doesn't work.

Can someone help me with correct way to add sub-partitions to an existing partitioned table (Range-List).

Jonathan Lewis

Re-read the thread.

Note particularly my first quick thumbnail sketch of actions:

Convert the partitioned table into a composite partitioned table with one subpartition per partition by using an EXCHANGE strategySplit each partition in turn - and 12.2 can split one partition into multiple subpartitions in one command.

If you want to transfer the data from a composite partitioned table to a simple partitioned table don't you think you will have to do something to reverse the "split partition"  before you do the exchanges ?

See also: https://jonathanlewis.wordpress.com/2019/05/23/re-partitioning/

Regards

Jonathan Lewis

Dom Brooks

https://oracle-base.com/articles/12c/online-conversion-of-a-non-partitioned-table-to-a-partitioned-table-12cr2

Ken18

Of-course i understand,

1) how to convert normal table to partitioned table

2) how to convert normal table to composite range-list partition [creates partitions and sub-partitions]

But i want to add sub-partitions (using list) to a table which already has partitions (range on createdate)

John Thorton

Ken18 wrote:Of-course i understand,1) how to convert normal table to partitioned table2) how to convert normal table to composite range-list partition [creates partitions and sub-partitions]But i want to add sub-partitions (using list) to a table which already has partitions (range on createdate) 

why?

What problem will sub partition solve?

Jonathan Lewis

You can't add subpartiitons to a table that is not already composite partitioned.

If you know how to convert a normal table to a partitioned table you can extend the method to convert a simple partitioned table to a composite partitioned table.

Regards

Jonathan Lewis

Ken18

Thank you very much, It is great to receive a reply from Oracle guru like you.

Thanks for the abundant info, I often go through your blogs when i need help.

Actually I am looking to,

Convert Pre-exisiting Partition tables (range) to be sub-partitioned (composite range-list)

1) As part of testing, Range Partitioned existing table using the below,

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">alter</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">table</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> PART_TEST modify </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">BY</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> RANGE </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">CREATEDATE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> p1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LESS THAN </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">TO_DATE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'15-MAY-2019'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'DD-MON-YYYY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> p2 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LESS THAN </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">TO_DATE</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'16-MAY-2019'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'DD-MON-YYYY'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> p3 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> LESS THAN </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">maxvalue</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">))</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> online</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

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

<span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ALTER</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">TABLE</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> PART_TEST MODIFY </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">PARTITION</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> P1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">ADD</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> SUBPARTITION SP1 </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">VALUES</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'1'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> tablespace TEST_PART</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span>

But I see this below 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

Jonathan Lewis

What version of Oracle are you on ?

Do you want to do this with virtually no interruption to end-users  - or do you have a reasonable time-window when you can deny access to the database ?

Regards

Jonathan Lewis

Ken18

Hi Sir,

I am on testing this on Oracle 12c,  I am working on prototype.

We intend to use these Oracle SQL scripts within our application, when user migrates from current application version to latest.. the existing partitions should have been sub-partitioned.

So there is a reasonable time window, but i would be keen to know both ways with no interruption to end-users as well.

And also would like to know on how to reverse migrate, sub-partitioned tables back to partitioned.

I mean when user is reverse migrating to  previous version from latest , 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

Donatello Settembrino

Hello,

some important information is missing (eg how big the table is) but if you have space and a time window without giving overhead to your system, in your place I'd do a CTAS with the partitioning/subpartitioning scheme you indicated in nologging + parallel mode.
At the end of the operations (at the right time) do the rename of the copy table with the new structure and gathered the statistics. The disservice time lasts only the time of rename table but you can do everything when and how you want.

Obviously with the same principle you can create the sql script for rollback operations.

Regards,

DS

Ken18

Thanks DS, but CTAS is not ideal for me as my schema is around 200GB (with BLOBS).

Can you shed somelight with any other alternatives if you could, like can we use exchange partitions or similar to convert parition tables to be sub-partitioned and revert back to partitioned table from sub-partitioned.

I am looking to Convert pre-existing Partitioned (range) table - to sub-partitioned (composite range-list)

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.

Thanks a ton for your time and help in this regards.

Donatello Settembrino

have you tried to take a look at the dbms_redefinition package?

Regards,

DS