Vector image by VectorStock / Anastasia8
This is something I read about and forgot until Chris Saxon mentioned and showcased it during todays AskTOM Office Hour session.
In Oracle 12.2 the create table command was enhanced to avoid the error
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
during an exchange partition operation. We can now do create table ... for exchange.
The basic idea is that the for exchange syntax enhancement considers things like invisible columns that are usually not created and by that it avoids complications during an exchange partition at a later time. For further details see this blog post by Connor McDonald.
Here I show a situation, where the ORA-14097 still happens, even if the for exchange syntax is used. Just something to be aware of and watch out for.
demo
First we create two identical partitioned tables, the source table A and the destination table B.
-- create the source table using NULLABLE columns
create table a (col1 number null, col2 number null, col3 number null)
PARTITION BY range (col1) interval (1)
(PARTITION old_data VALUES LESS THAN (0)
);
;
-- add a primary key constraint
alter table a add primary key (col1, col2);
desc a; Name Null? Typ ---- -------- ------ COL1 NOT NULL NUMBER COL2 NOT NULL NUMBER COL3 NUMBER
As you can see the table looks as if col1 and col2 are not null. This is because the primary key requires all columns to be not null.
Now we do exactly the same for table B. in my real world case scenarion table A was on a remote database and table B was a local one, created by a tablespace transport. So source and destination are identical.
create table b (col1 number null, col2 number null, col3 number null)
PARTITION BY range (col1) interval (1)
(PARTITION old_data VALUES LESS THAN (0)
);
;
alter table b add primary key (col1, col2);
Now we fill the source table A with some dummy data.
-- add some data into a
insert into a (col1, col2, col3)
select mod(level,10) ,level, mod(level,100) from dual connect by level <= 1000;
1000 rows inserted.
commit;
Commit finished.
Next step is to create and fill a staging table e which will hold the partition that we want to add to B.
Notice that during the create table command the FOR EXCHANGE syntax is used. Unfortunatly it seems we can not do a CTAS when using the syntax enhancement, so two commands are used: create + insert.
create table e for exchange with table b;
insert into e select * from a partition for (9);
Side note: Because the table is interval partitioned, here “partition for” is useful to specify the source partition.
desc e; Name Null? Typ ---- ----- ------ COL1 NUMBER COL2 NUMBER COL3 NUMBER
The exchange table e is declared with NULL columns. Although this reflects the original table without the PK, it will lead to a problem during the exchange.
alter table b exchange partition for (9) with table e;
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
*Cause: The corresponding columns in the tables specified in the
ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action: Ensure that the two tables have the same number of columns
with the same type and size.
This error will not happen if we do the same commands, but without adding the primary key constraint.
Conclusion
Even when using for exchange, you can still run into the “mismatched columns” problem (ORA-14097). In this particular example the problem is, that the addition of the primary key converts the key columns to NOT NULL. But this conversion is not reflected in the CREATE TABLE .. FOR EXCHANGE command.
Btw: The error can be avoided by creating the columns with NOT NULL in the first place.
svenweller 
Once again nice article.
Hello Sven,
It looks like there is a small discrepancy between the logic of how FOR EXCHANGE works, vs
how the ALTER TABLE EXCHANGE PARTITION works.
The fact is that, when you define a column as a PRIMARY KEY, it will be shown (“externally”) as
NOT NULLABLE, even if you do not define it as NOT NULL, but it does NOT effectively have a check constraint with the “IS NOT NULL” condition.
You can see this from the following:
create table t1 (c1 number not null primary key, c2 number)
/
Table created.
create table t2 (c1 number primary key, c2 number)
/
Table created.
select table_name, column_name, nullable
from user_tab_columns
where table_name in (‘T1′,’T2’)
/
TABLE_NAME COLUMN_NAME NULLABLE
———————————————————————
T1 C1 N
T1 C2 Y
T2 C1 N
T2 C2 Y
4 rows selected.
select table_name, constraint_type, search_condition
from user_constraints
where table_name in (‘T1′,’T2’)
/
TABLE_NAME CONSTRAINT_TYPE SEARCH_CONDITION
——————————————————————————————-
T1 C “C1” IS NOT NULL
T1 P –
T2 P –
3 rows selected.
It looks like the CREATE TABLE … FOR EXCHANGE follows the “real” (or “internal”) column definition including the existing CHECK constraints, while the ALTER TABLE EXCHANGE PARTITION
looks at the “external” NULLABLE property of the two tables, therefore they appear different
and cause the error to be raised.
Cheers & Best Regards,
Iudith Mentzel
Hi Iudith,
Yes this is exactly the problem. The expectation, or lets say the “promise” that the FOR EXCHANGE syntax gives us, is not fullfilled in this specific case. Although I understand why this happens I feel that this case is not so different from a virtual column for example.