how to fix Errors on composite partition by list- to list

[grapes12]

Receiving duplicate errors when trying to create a composite partition by list - to list
ORA-14615: List value '01' specified twice in subpartitions 'L20M_P3_01', 'L20M_P6_01'

herewith is the create script

PARTITION BY LIST (POSTING_YEAR)
SUBPARTITION BY LIST (POSTING_PERIOD)
SUBPARTITION TEMPLATE (
    SUBPARTITION L20M_P1_01 VALUES (01),
    SUBPARTITION L20M_P1_02 VALUES (02),
    SUBPARTITION L20M_P1_03 VALUES (03),
    SUBPARTITION L20M_P1_04 VALUES (04),
    SUBPARTITION L20M_P1_05 VALUES (05),
    SUBPARTITION L20M_P1_06 VALUES (06),
    SUBPARTITION L20M_P1_07 VALUES (07),
    SUBPARTITION L20M_P1_08 VALUES (08),
    SUBPARTITION L20M_P1_09 VALUES (09),
    SUBPARTITION L20M_P1_10 VALUES (10),
    SUBPARTITION L20M_P1_11 VALUES (11),
    SUBPARTITION L20M_P1_12 VALUES (12), 
    SUBPARTITION L20M_P2_01 VALUES (01),
    SUBPARTITION L20M_P2_02 VALUES (02),
    SUBPARTITION L20M_P2_03 VALUES (03),
    SUBPARTITION L20M_P2_04 VALUES (04),
    SUBPARTITION L20M_P2_05 VALUES (05),
    SUBPARTITION L20M_P2_06 VALUES (06),
    SUBPARTITION L20M_P2_07 VALUES (07),
    SUBPARTITION L20M_P2_08 VALUES (08),
    SUBPARTITION L20M_P2_09 VALUES (09),
    SUBPARTITION L20M_P2_10 VALUES (10),
    SUBPARTITION L20M_P2_11 VALUES (11),
    SUBPARTITION L20M_P2_12 VALUES (12), 
    SUBPARTITION L20M_P3_01 VALUES (01),
    SUBPARTITION L20M_P3_02 VALUES (02),
    SUBPARTITION L20M_P3_03 VALUES (03),
    SUBPARTITION L20M_P3_04 VALUES (04),
    SUBPARTITION L20M_P3_05 VALUES (05),
    SUBPARTITION L20M_P3_06 VALUES (06),
    SUBPARTITION L20M_P3_07 VALUES (07),
    SUBPARTITION L20M_P3_08 VALUES (08),
    SUBPARTITION L20M_P3_09 VALUES (09),
    SUBPARTITION L20M_P3_10 VALUES (10),
    SUBPARTITION L20M_P3_11 VALUES (11),
    SUBPARTITION L20M_P3_12 VALUES (12), 
    SUBPARTITION L20M_P4_01 VALUES (01),
    SUBPARTITION L20M_P4_02 VALUES (02),
    SUBPARTITION L20M_P4_03 VALUES (03),
    SUBPARTITION L20M_P4_04 VALUES (04),
    SUBPARTITION L20M_P4_05 VALUES (05),
    SUBPARTITION L20M_P4_06 VALUES (06),
    SUBPARTITION L20M_P4_07 VALUES (07),
    SUBPARTITION L20M_P4_08 VALUES (08),
    SUBPARTITION L20M_P4_09 VALUES (09),
    SUBPARTITION L20M_P4_10 VALUES (10),
    SUBPARTITION L20M_P4_11 VALUES (11),
    SUBPARTITION L20M_P4_12 VALUES (12), 
    SUBPARTITION L20M_P5_01 VALUES (01),
    SUBPARTITION L20M_P5_02 VALUES (02),
    SUBPARTITION L20M_P5_03 VALUES (03),
    SUBPARTITION L20M_P5_04 VALUES (04),
    SUBPARTITION L20M_P5_05 VALUES (05),
    SUBPARTITION L20M_P5_06 VALUES (06),
    SUBPARTITION L20M_P5_07 VALUES (07),
    SUBPARTITION L20M_P5_08 VALUES (08),
    SUBPARTITION L20M_P5_09 VALUES (09),
    SUBPARTITION L20M_P5_10 VALUES (10),
    SUBPARTITION L20M_P5_11 VALUES (11),
    SUBPARTITION L20M_P5_12 VALUES (12), 
    SUBPARTITION L20M_P6_01 VALUES (01),
    SUBPARTITION L20M_P6_02 VALUES (02),
    SUBPARTITION L20M_P6_03 VALUES (03),
    SUBPARTITION L20M_P6_04 VALUES (04),
    SUBPARTITION L20M_P6_05 VALUES (05),
    SUBPARTITION L20M_P6_06 VALUES (06),
    SUBPARTITION L20M_P6_07 VALUES (07),
    SUBPARTITION L20M_P6_08 VALUES (08),
    SUBPARTITION L20M_P6_09 VALUES (09),
    SUBPARTITION L20M_P6_10 VALUES (10),
    SUBPARTITION L20M_P6_11 VALUES (11),
    SUBPARTITION L20M_P6_12 VALUES (12) TABLESPACE MAC_P) (
PARTITION L20M_2006_2012 VALUES (2006,2012),
PARTITION L20M_2007_2013 VALUES (2007,2013),
PARTITION L20M_2008_2014 VALUES (2008,2014),
PARTITION L20M_2009_2012 VALUES (2009,2015),
PARTITION L20M_2010_2016 VALUES (2010,2016),    
PARTITION L20M_2011_2017 VALUES (2011,2017))   
NOCOMPRESS 
NOCACHE
NOPARALLEL 
MONITORING;

ANy reason why it would give these errors when the subpartition in my opinion have unique names?????

 

[Dagon]

I haven't got 11g, so I can't try this out but I think it's because you've got duplicate values in the list in the subpartition template e.g.

SUBPARTITION L20M_P4_02 VALUES (02),
SUBPARTITION L20M_P6_02 VALUES (02),

You've misunderstood the idea of the subpartition template. It defines a template which will apply to all partitions, so each of L20M_2006_2012, L20M_2007_2013 etc will get the set of subpartitions that you've specified generated from them. Obviously, if there are two (02) partitions, that is going to cause a problem. You just have to define each subpartition value once in the template and Oracle will automatically give them unique names across all the partitions.


For Oracle-related work, contact me through Linked-In.

 

[grapes12]

Thank you
I really did mis-understand the subpartition template.
Once I removed template syntax,I was able to create the table.
thanks

 

[SantaMufasa]

Grapes,

Our "currency" here on Tek-Tips to reward good advice or excellent "catches" is to award a

by clicking the Thank Dagon for this valuable post link. Dagon really did make an excellent catch.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”