'Oracle - Error on EXCHANGE PARTITION with List-Range partitioning
I'm trying to use EXCHANGE PARTITION on following example schema:
CREATE TABLE TEMP_TABLE_00(
CONTACT_ID NUMBER,
SECONDARY_CONTACT_FLAG NUMBER,
SOURCE_SYSTEM VARCHAR2(10 CHAR),
START_DATE DATE,
ACTIVE NUMBER,
SECONDARY_CONTACT_FLAGS NUMBER GENERATED ALWAYS AS (ACTIVE || SECONDARY_CONTACT_FLAG) VIRTUAL,
CONSTRAINT pk_temp_table_00 PRIMARY KEY (CONTACT_ID))
PARTITION BY LIST (SECONDARY_CONTACT_FLAGS)
SUBPARTITION BY RANGE (START_DATE)
( PARTITION p_ac_00 VALUES ('00')
( SUBPARTITION sp_ac_00_before_2014 VALUES LESS THAN (TO_DATE ('01.01.2014', 'DD.MM.YYYY')),
SUBPARTITION sp_ac_00_201401 VALUES LESS THAN (TO_DATE ('01.02.2014', 'DD.MM.YYYY'))
)
)
ENABLE ROW MOVEMENT;
create table TARGET_TABLE(
CONTACT_ID NUMBER,
SECONDARY_CONTACT_FLAG NUMBER,
SOURCE_SYSTEM VARCHAR2(10 CHAR),
START_DATE DATE,
ACTIVE NUMBER,
SECONDARY_CONTACT_FLAGS NUMBER GENERATED ALWAYS AS (ACTIVE || SECONDARY_CONTACT_FLAG) VIRTUAL,
CONSTRAINT pk_target_table PRIMARY KEY (CONTACT_ID))
PARTITION BY LIST (secondary_contact_flags)
SUBPARTITION BY RANGE (START_DATE)
( PARTITION p_ac_00 VALUES ('00')
( SUBPARTITION sp_ac_00_before_2014 VALUES LESS THAN (TO_DATE ('01.01.2014', 'DD.MM.YYYY')),
SUBPARTITION sp_ac_00_201401 VALUES LESS THAN (TO_DATE ('01.02.2014', 'DD.MM.YYYY'))
),
PARTITION p_ac_10 VALUES ('10')
( SUBPARTITION sp_ac_10_before_2014 VALUES LESS THAN (TO_DATE ('01.01.2014', 'DD.MM.YYYY')),
SUBPARTITION sp_ac_10_201401 VALUES LESS THAN (TO_DATE ('01.02.2014', 'DD.MM.YYYY'))
),
PARTITION p_ac_01 VALUES ('01')
( SUBPARTITION sp_ac_01_before_2014 VALUES LESS THAN (TO_DATE ('01.01.2014', 'DD.MM.YYYY')),
SUBPARTITION sp_ac_01_201401 VALUES LESS THAN (TO_DATE ('01.02.2014', 'DD.MM.YYYY'))
),
PARTITION p_ac_11 VALUES ('11')
( SUBPARTITION sp_ac_11_before_2014 VALUES LESS THAN (TO_DATE ('01.01.2014', 'DD.MM.YYYY')),
SUBPARTITION sp_ac_11_201401 VALUES LESS THAN (TO_DATE ('01.02.2014', 'DD.MM.YYYY'))
)
)
ENABLE ROW MOVEMENT;
INSERT INTO DM_KSCTSC.TEMP_TABLE_00 (CONTACT_ID, SECONDARY_CONTACT_FLAG, SOURCE_SYSTEM, START_DATE, ACTIVE) VALUES (1, 0, 'ABC', TO_DATE('20140101', 'YYYYMMDD'), 0);
commit;
When trying to exchange partitions:
alter table target_table exchange partition p_ac_00 with table TEMP_TABLE_00 without validation;
even though the structure of the partitions are identical, Oracle returns an
ORA-14292: Partitioning type of table must match subpartitioning type of composite partition
Is there another solution than exchanging every subpartition seperately?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
