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.
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
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.
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.