Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Add subpartitions to existing partition table

oraLaroJul 1 2020 — edited Jul 2 2020

12.2

Is there an easy to add subpartitions to an existing partitioned table in 12.2

This is existing table

create table

t_already_there (

   start_date        DATE,

   store_id          NUMBER,

   keep_flag         varchar2(1)

)

PARTITION BY RANGE (start_date)

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

(

   PARTITION P1_DATE1 VALUES LESS THAN (TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

);

I want to add sub_partition on keep_flag where values would go a Y subpartition if Y or if anything else go to a default subpartition.   Could have several hundred partitions in tables Im doing this too.

If I created from new heres how it would look and this works

create table t_new

(

   start_date        DATE,

   store_id          NUMBER,

   keep_flag         varchar2(1)

)

PARTITION BY RANGE (start_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

SUBPARTITION BY LIST (keep_flag)

SUBPARTITION TEMPLATE

   ( SUBPARTITION p_t VALUES ('Y')

   , SUBPARTITION to_others values (default)

   )

PARTITION P1_DATE1 VALUES LESS THAN (TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

);

yes I could create new table and either redefine or insert and rename tables but interested to see can we do an alter table modify and the subpartitions.   I cant documentation that shows how.  thanks

This post has been answered by Jonathan Lewis on Jul 1 2020
Jump to Answer
Comments
Post Details
Added on Jul 1 2020
2 comments
2,315 views