ORA-14097 At Exchange Partition After Adding Column With Default Value (Doc ID 1334763.1)

Last updated on MAY 31, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.
*** Checked for relevance 05-Feb-2016 ***

Symptoms

- Exchange partition errors out with ORA-14097

create table MTAB
(PCOL number)
PARTITION BY LIST (PCOL)
(PARTITION "P1" VALUES (1),
PARTITION "P2" VALUES (2));

alter table MTAB add COL2 number DEFAULT 0 NOT NULL;

create table MTAB_P2 as select * from MTAB where 1=0;

-- No difference in the outcome if the table is created with non CTAS
-- create table MTAB_P2
-- (PCOL number,
-- COL2 number default 0 not null);

alter table MTAB exchange partition P2 with table MTAB_P2;


alter table MTAB exchange partition P2 with table MTAB_P2
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms