You Asked

Hello Chris, hello Connor.

Almost every time I implement a ETL process using partition exchange I bang my head against an ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.

This time I cannot figure out what the reason for it is. The exchange table is created by CTAS * statement, no invisible columns, so the columns themselves must match. I compared them and they do match. There is no primary key on the target table. All the constraints match.

The most confusing thing is: I have created a complete copy of the target table, both DDL and data. And on this table CTAS * + the exchange partition statement does work! Regardless of whether I make an exchange table as CTAS * from the original table or the copy. For this reason I am unable to provide you with an example that reproduces the issue, sorry.

What can be a reason for such behavior?

Is there a way to create an exchange table better than CTAS *?

Are there any better ways for diagnosing ORA-14097 beyond 'check your columns'?

As attractive as this load method is, implementing it is a pain every time, mainly because ORA-14097 message lacks any details.

and Chris said...

By any chance do you have any unused columns on the partitioned table?

You can spot these by looking in *_tab_cols and looking for SYS...$ named columns:

create table tgt (
  c1 int, c2 int
) partition by hash ( c1 ) (
  partition p1
);

alter table tgt
  set unused column c2;
  
select column_name 
from  user_tab_cols
where  table_name = 'TGT';

COLUMN_NAME                  
C1                            
SYS_C00002_20120310:32:37$

This will also reveal any other system generated columns which may be causing issues.

Although you can't access unused columns, they do prevent partition exchange:

create table src as 
  select * from tgt;
  
insert into src
with rws as (
  select level x from dual
  connect by level <= 3
)
  select * from rws;
commit;
  
alter table tgt 
  exchange partition p1 
  with table src;
  
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

From 12.2 you can get around this using the create table ... for exchange command:

drop table src;

create table src
  for exchange 
  with table tgt;
  
insert into src
with rws as (
  select level x from dual
  connect by level <= 3
)
  select * from rws;
commit;
  
alter table tgt 
  exchange partition p1 
  with table src;
    
select * from tgt;

C1   
    1 
    2 
    3

As you're on 12.1, drop the unused columns from the partitioned table:

truncate table tgt;
drop table src;
alter table tgt
  drop unused columns;

create table src as 
  select * from tgt;
  
insert into src
with rws as (
  select level + 10 x from dual
  connect by level <= 3
)
  select * from rws;
commit;
  
alter table tgt 
  exchange partition p1 
  with table src;
    
select * from tgt;

C1   
   11 
   12 
   13

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

No unused columns

Mikhail Onishchenko, December 03, 2020 - 10:58 am UTC

Hey Chris, thank you for the answer. There are no unused columns on the table, but I may have an idea to what have caused this problem.

Couple months ago I added some columns to this table and then rearranged their column_id's by making some of the columns invisible and then visible again.

Could this cause the problem?

Is there another way besides recreating a table to change column_id's? I know that I shouldn't care about the default order of the columns in the relational model, but my sense of beauty compels me to organize columns neatly.

Thank you for the 'for exchange' piece of syntax. Another reason to push for an upgrade.

December 04, 2020 - 3:15 am UTC

Check user_tab_cols to get a precise list of the columns (hidden and otherwise).

Then you need to create an exchange table that has those exact attributes (or you modify the primary table to "clean" it up).

eg

SQL> desc PAR

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      NUMBER(10)
 Y                                      NUMBER(10)

SQL> select partition_name
  2  from   user_tab_partitions
  3  where  table_name = 'PAR';

PARTITION_NAME
------------------------------------------------------------
P1
P2

2 rows selected.

SQL> create table EXCH_PAR
  2  as select * from PAR
  3  where 1=0;

Table created.

SQL> desc EXCH_PAR

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      NUMBER(10)
 Y                                      NUMBER(10)

SQL> alter table PAR exchange partition P1
  2    with table EXCH_PAR;

alter table PAR exchange partition P1
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE

SQL> select column_id, column_name, data_type
  2  from   user_tab_cols
  3  where  table_name = 'PAR';

 COLUMN_ID COLUMN_NAME                    DATA_TYPE
---------- ------------------------------ ---------
         1 X                              NUMBER
           SYS_C00002_16083109:09:55$     DATE
         2 Y                              NUMBER

--
-- because originally the table was like this , and then I marked a column unused
--
SQL> desc PAR
 Name                          Null?    Type
 ----------------------------- -------- -----------
 X                                      NUMBER(10)
 Z                                      DATE
 Y                                      NUMBER(10)

SQL> alter table PAR set unused column Z;

SQL> create table EXCH_PAR ( 
  2      x     number(10), 
  3      dummy date , 
  4      y     number(10));

Table created.

SQL> alter table EXCH_PAR set unused column dummy;

Table altered.

SQL> alter table PAR exchange partition P1
  2    with table EXCH_PAR;

Table altered.

But its not *just* unused column - could be virtual columns, function based indexes, stats extensions etc etc....Until 12.2, you need to replicate the "internal" things that have happened exactly

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

No unused columns

More to Explore

Administration

ETL using exchange partition and ORA-14097

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

No unused columns

More to Explore

Administration