Oracle Database Discussions

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

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)

Ken18 wrote:
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) 

why?

What problem will sub partition solve?

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

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,

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 tried to alter the table to add sub-partitions (list) using the below,

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

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

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

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

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

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.

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

Regards,

DS

If space is the biggest issue then (quick thumbnail sketch).

Convert the partitioned table into a composite partitioned table with one subpartition per partition by using an EXCHANGE strategy

Split each partition in turn - and 12.2 can split one partition into multiple subpartitions in one command.

Change the subpartition template to handle future partitions.

This avoids the need of ever having two copies of the data anywhere. (Otherwise for 12.2 you can do an "alter table modify" to convert the partitioned table to composite partitioned but that gives you two copies and a journal and a load of duplicated index space). [Update: My error - simple partitioned to composite partitioned only appeared in 18c and there's a bug that needs the user to have a quota on the SYSTEM tablespace. This is fixed by 19.2]

Quick example:

create table pt_range (        id              number(8,0)     not null,        grp             varchar2(1)     not null,        small_vc        varchar2(10),        padding         varchar2(100))partition by range(id) (        partition p200 values less than (200),        partition p400 values less than (400),        partition p600 values less than (600));insert into pt_rangeselect        rownum-1,        mod(rownum,2),        lpad(rownum,10,'0'),        rpad('x',100,'x')from        all_objectswhere        rownum <= 600;commit;create table pt_range_list (        id              number(8,0)     not null,        grp             varchar2(1)     not null,        small_vc        varchar2(10),        padding         varchar2(100))partition by range(id) subpartition by list (grp) subpartition template (        subpartition p_def      values(default))(        partition p200 values less than (200),        partition p400 values less than (400),        partition p600 values less than (600));create table t for exchange with table pt_range;alter table pt_range exchange partition p200 with table t;alter table pt_range_list exchange subpartition p200_p_def with table t;alter table pt_range exchange partition p400 with table t;alter table pt_range_list exchange subpartition p400_p_def with table t;alter table pt_range exchange partition p600 with table t;alter table pt_range_list exchange subpartition p600_p_def with table t;

When you do the exchange you'll have to decide what to do about indexes and validation, of course, but for a quick "no-users" job I probably wouldn't maintain any global indexes I didn't have to, but I would take the local indexes into the exchansge, then rebuild the global indexes at the end.

Regards

Jonathan Lewis

Hi Sir,

Thank you very much for your precious time and for the detailed steps.

It really helped me, but at last step - EXCHANGE subpartition it fails (error states column type or size mismatch).

(1) My existing table is IMAGE and i converted it using below to range partitioned table.

alter table IMAGE_RANGE

modify PARTITION BY RANGE (CREATEDATE)

(

PARTITION p1 VALUES LESS THAN (TO_DATE('22-MAY-2019', 'DD-MON-YYYY')),

PARTITION p2 VALUES LESS THAN (TO_DATE('23-MAY-2019', 'DD-MON-YYYY'))

);

(2) Created Range-List subpartitioned table using IMAGE Table DDL SQL,

CREATE TABLE IMAGE_RANGE_LIST"

( "ID" NUMBER(20,0) NOT NULL ENABLE,

"VERSION" NUMBER(38,0) NOT NULL ENABLE,

"CLASSID" NUMBER(38,0),

"ITEMID" NUMBER(20,0),

"ORGID" NUMBER(20,0),

"CREATEDATE" TIMESTAMP (6) NOT NULL ENABLE,

"LASTMODIFYDATE" TIMESTAMP (6) NOT NULL ENABLE,

"TYPECODE" NUMBER(38,0) NOT NULL ENABLE,

"ROTATION" NUMBER(38,0) NOT NULL ENABLE,

"CLASSIFICATIONNUMBER" NUMBER(38,0),

"SEQUENCENUMBER" NUMBER(38,0),

"DATA" BLOB,

"DATALEGACY" BLOB,

"SITEID" NUMBER(1,0) NOT NULL ENABLE,

"IMAGEPROCESS" VARCHAR2(32 BYTE),

"PURGEPROFILE" NUMBER(38,0) NOT NULL ENABLE,

"PURGEID" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE

)

PARTITION BY RANGE(createdate)

SUBPARTITION BY LIST(PURGEID)

SUBPARTITION TEMPLATE

(

SUBPARTITION s1 values(default)

)

(

 PARTITION p1 VALUES LESS THAN (TO\_DATE('22-MAY-2019', 'DD-MON-YYYY')),

PARTITION p2 VALUES LESS THAN (TO\_DATE('23-MAY-2019', 'DD-MON-YYYY'))

) TABLESPACE TEST_PART enable row movement;

(3) Created the interim table,

create table IMAGE_T TABLESPACE TEST_PART for exchange with table IMAGE_RANGE;

(4) Now when I tried to use exchange partition, for subpartition it fails saying column type or size mismatch.

NOTE: I have thoroughly checked the columns and data types and other details, the tables have 17 columns with the same attributes.

Could it be a generic error by any chance? or am I missing anything?

Kindly shed some light.

Thank you.

This is exactly why Oracle created the create table for exchange with syntax. Are you able to create another table using this syntax and then convert it into a composite partitioned table? I've not tried this but I don't see it being ruled out based on what's been said here.

Otherwise:

Are the columns in the exact same order?

Are you making sure you are comparing using the same length semantics (bytes vs chars)?

Are you making sure columns have the same nullability?

Are there unused columns in the original table? (select * from dba_unused_col_tabs)

Share the DDL using dbms_metadata for both tables, we only need the column lists so don't paste in the huge chunk that follows it.

You've run into a variation on a common problem that I'll be writing about in the next few days.

Over time a table can end up with all sorts s of strange bits and pieces of history so that when you describe it and try to create a copy of it the stuff that ends up in the data dictionary for the copy doesn't match the original - even though to the external view things look identical. This is why Oracle introduced the "create table for exchange.." option.

It's probably the case that your range/list definition is internally inconsistent with the range table, so try the following

a) create a simple table for exchange

b) modify the table to become a range/list partitioned table

c) create another table for exchange

You should now be able to exchange from the original to the second (c) table, then from the second (c) table to the range/list (a/b) table.

e.g. - allowing for typos:

create table image_range_list for exchange with table image_range;

alter table image_range_list  modify

PARTITION BY RANGE(createdate)

  SUBPARTITION BY LIST(PURGEID)

  SUBPARTITION TEMPLATE

  (

    SUBPARTITION s1 values(default)

  )

(

     PARTITION p1 VALUES LESS THAN (TO_DATE('22-MAY-2019', 'DD-MON-YYYY')),

    PARTITION p2 VALUES LESS THAN (TO_DATE('23-MAY-2019', 'DD-MON-YYYY'))

)

;

create table image_simple for exchange with table image_range;

alter table image_range exchange partition p1 with table image_simple;

alter table image_range_list exchange partition p1 with table image_simple;

etc.

Don't forget you have to allow for indexes (if you have any) and there is a "validation" clause - so it's just occured to me that the error message you were getting might have been a little misleading and something to do with an indexing difference between image_range and image_range_list, so (e.g.)

alter table image_range exchange partition p1 with table image_simple excluding indexes without validation;

Regards

Jonathan Lewis

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]

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;

alter table IMAGE_RANGE exchange partition p2 with table IMAGE_T excluding indexes without validation;

alter table IMAGE_RANGE_LIST exchange subpartition p2_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';

Not sure what is that I am missing here, can you please help with this.

I do not have a clear image of what you've done and what you're trying to say.

Show us the entire script that

a) creates the simple exchange table, the

b) creates the empty copy of the final table

c) selects list of partition names with num_rows and high_values from the initial table

d) selects count(*) from first partition you want to exchange, does two exchanges, then selects count(*) from the relevant partition in the final table

e) ... repeat (d) for the remaining partitions.

Then show us the output you get from running that script

Regards

Jonathan Lewis

Thanks for clearing the confusion, my bad I got confused and was checking the data and partitions in the interim table instead of the final table.

Again going through your replies clarified things,

" You should now be able to exchange from the original to the second (c) table, then from the second (c) table to the range/list (a/b) table. "

Could you review the below incase we need to get one step back to partition level --reverse Migration:

---------------------------------------------------------------------------------------------------------------------------------------

Composite - Range partitioned table with sub-partitions to the partitioned table (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)

My original table with data changed to partitioned for my testing :

=================================================

alter table IMAGE

modify PARTITION BY RANGE (CREATEDATE)

SUBPARTITION BY LIST (PURGEID)

subpartition template(

SUBPARTITION s1 values (0),

SUBPARTITION s2 values (1),

SUBPARTITION s3 values (2),

SUBPARTITION s4 values (3))

(

PARTITION p1 VALUES LESS THAN (TO_DATE('23-MAY-2019', 'DD-MON-YYYY')),

PARTITION p2 VALUES LESS THAN (TO_DATE('24-MAY-2019', 'DD-MON-YYYY')),

PARTITION p3 VALUES LESS THAN (maxvalue)) online;

-- Final table (data from sub-partition of my existing table has to be moved to this during reverse migration):

=================================================================================

create table IMAGE_NEW for exchange with table IMAGE;

alter table IMAGE_NEW

modify PARTITION BY RANGE (CREATEDATE)

(

PARTITION p1 VALUES LESS THAN (TO_DATE('23-MAY-2019', 'DD-MON-YYYY')),

PARTITION p2 VALUES LESS THAN (TO_DATE('24-MAY-2019', 'DD-MON-YYYY')),

PARTITION p3 VALUES LESS THAN (maxvalue)) online;

-- Interim table IMAGE_TEMP for exchange :

==================================

create table IMAGE_TEMP for exchange with table IMAGE;

-- Exchange partitions using the interim table :

===================================

alter table IMAGE_NEW exchange partition p1 with table IMAGE_TEMP;

alter table IMAGE exchange subpartition p1_s1 with table IMAGE_TEMP;

alter table IMAGE_NEW exchange partition p1 with table IMAGE_TEMP;

alter table IMAGE exchange subpartition p1_s2 with table IMAGE_TEMP;

alter table IMAGE_NEW exchange partition p1 with table IMAGE_TEMP;

alter table IMAGE exchange subpartition p1_s3 with table IMAGE_TEMP;

alter table IMAGE_NEW exchange partition p1 with table IMAGE_TEMP;

alter table IMAGE exchange subpartition p1_s4 with table IMAGE_TEMP;

Appreciate your quick help on this.

Thanks a ton for all the support so far.

Yes, I just figured it out yesterday... that multiple sub-partitions can't be exchanged with a single partition if we wish to get back/reverse migrate one step back to partition level.

Before we exchange we need to merge all the sub-partitions to single subpartition and exchange it with the partitions of a range partitioned table.

KEY OBSERVATION: THE ORDER OF EXCHANGE IS CRITICAL TO GET THE DESIRED OUTCOME, ELSE THE EXECUTION WORKS BUT WE END UP WITH DIFF RESULTS.

the above commands in my previous comment change as below respectively.

alter table IMAGE exchange subpartition p1_s1 with table IMAGE_TEMP;

alter table IMAGE_NEW exchange partition p1 with table IMAGE_TEMP;

Thanks a lot once again, this conversation with you was very helpful for clearing the confusions and getting to the right approach.

This community also helped me many times when needed, thanks to all the active experts here who always take their time and help us by sharing your knowledge.