我正在尝试在以下示例架构中使用 EXCHANGE PARTITION:

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;

尝试交换分区时:

alter table target_table exchange partition p_ac_00 with table TEMP_TABLE_00 without validation;

即使分区的结构相同，Oracle 也会返回一个

ORA-14292: Partitioning type of table must match subpartitioning type of composite partition

除了分别交换每个子分区之外，还有其他解决方案吗？

最佳答案

为了能够在分区表 (PT) 和非分区表 (TMP) 之间交换分区，TMP 表必须具有与 PT 分区相同的结构。

即如果 PT 是分区的，则 TMP 是非分区的。

如果 PT 是复合分区，则 TMP 的分区方式与 PT 的子分区相同。

在您的情况下，问题出在 TMP 表 (TEMP_TABLE_00) 中，它必须按 RANGE 分区(与 TARGET_TABLE 的子分区相同)

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  RANGE (START_DATE)
  
  (  PARTITION sp_ac_00_before_2014  VALUES LESS THAN (TO_DATE ('01.01.2014', 'DD.MM.YYYY')),
     PARTITION sp_ac_00_201401       VALUES LESS THAN (TO_DATE ('01.02.2014', 'DD.MM.YYYY'))
  )    
 
ENABLE ROW MOVEMENT;

在此设置中，交换分区起作用。

https://stackoverflow.com/questions/35748998/