Error while exchanging partition in Oracle ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Question

I am trying to swap partition PART_201901 from the MAIN_TABLE to the TABLE_HISTORY.

I have created the TABLE_HISTORY using the same script as MAIN_TABLE, literally used the same script, indexes, constraints.

My Main_Table has PARTITION BY LIST (YEAR_MONTH) where the YEAR_MONTH is a number field with the value 201901, 201902 etc. and the paritition names are PART_201901 and so on...

• 2 Local Index,
• 1 Local Unique Index,
• 1 PK Constraint using the Local Unique Index.

I am using the below query to exchange the partition:

step1: create table tmp_swap  for exchange with table MAIN_TABLE

step2: ALTER TABLE MAIN_TABLE EXCHANGE PARTITION PART_201901 WITH TABLE tmp_swap

step3: ALTER TABLE TABLE_HISTORY EXCHANGE PARTITION PART_201901 WITH TABLE tmp_swap

I am getting the following error in step3:

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

I have reviewed if there is any difference between the 2 tables using the below query and its matching with each other.

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,COLUMN_ID FROM SYS.dba_tab_cols where table_name = 'MAIN_TABLE'  order by column_id

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,COLUMN_ID FROM SYS.dba_tab_cols where table_name = 'TABLE_HISTORY' order by column_id

select table_Name,search_condition_vc,constraint_type from user_constraints where table_name = 'MAIN_TABLE' order by search_Condition_vc

select table_Name,search_condition_vc,constraint_type from user_constraints where table_name = 'TABLE_HISTORY' order by search_Condition_vc

I have checked other answers and I am unable to identify what is wrong here.

Answer 1

The create table tmp_swap for exchange with table MAIN_TABLE command only creates the correct internal column structure. It does not create any dependent objects you may also need.

Because you have a PK on the main table, you must also have a PK on the swap table, and it must be enabled so that requires a valid index. Create the unique index on the swap table, add the PK constraint using that index, then retry the exchange