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

During partitioning of an existing table, I've got a strange error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.
Generally this error means that column order, data type, default etc. mismatch between the two table. But as you can see in below steps this is not the case here:

SQL> CREATE TABLE xyz
  2  (a INT,
  3  b VARCHAR2(30))
  4  /

Table created

SQL>
SQL> INSERT INTO xyz
  2  SELECT ROWNUM, object_name FROM user_objects
  3  /

2842 rows inserted

SQL> commit;

Commit complete

SQL> ALTER TABLE xyz ADD ( c NUMBER(19) DEFAULT 0 NOT NULL );

Table altered

SQL>
SQL>   CREATE TABLE XYZ_PART
  2     (a INT,
  3     b VARCHAR2(30),
  4     c NUMBER(19) DEFAULT 0 NOT NULL)
  5    PARTITION BY RANGE(C)
  6    (
  7      PARTITION P1 VALUES LESS THAN (100) ,
  8        PARTITION P2 VALUES LESS THAN (1000),
  9        PARTITION PM VALUES LESS THAN (MAXVALUE)
 10    )
 11  /

Table created

SQL>
SQL> ALTER TABLE XYZ_PART
  2  EXCHANGE PARTITION P1
  3  WITH TABLE xyz
  4  /

ALTER TABLE XYZ_PART
EXCHANGE PARTITION P1
WITH TABLE xyz

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


The issue here is due to the fact that we have added a partition key column with default values after creating the table. Simple workaround would be add column without any default or not null clause and then partition the table and at last add required default and not null clause. like this:


SQL> CREATE TABLE xyz
  2  (a INT,
  3  b VARCHAR2(30))
  4  /

Table created
SQL> INSERT INTO xyz
  2  SELECT ROWNUM, object_name FROM user_objects
  3  /

2851 rows inserted
SQL> ALTER TABLE xyz ADD ( c NUMBER(19) )
  2  /

Table altered
SQL> UPDATE xyz
  2  SET c = 0
  3  /

2851 rows updated
SQL> CREATE TABLE XYZ_PART
  2  (a INT,
  3  b VARCHAR2(30),
  4  c NUMBER(19) )
  5  PARTITION BY RANGE(C)
  6  (
  7  PARTITION P1 VALUES LESS THAN (10000) ,
  8  PARTITION P2 VALUES LESS THAN (100000),
  9  PARTITION PM VALUES LESS THAN (MAXVALUE)
 10  )
 11  /

Table created
SQL> ALTER TABLE XYZ_PART
  2  EXCHANGE PARTITION P1
  3  WITH TABLE xyz
  4  /

Table altered

SQL> drop table xyz;

Table dropped

SQL> alter table xyz_part rename to xyz;

Table altered

SQL> alter table xyz modify c default 0 not null;

Table altered
---------------------------------------------------------------------------------------------


For more information check these links:

http://oraganism.wordpress.com/2012/06/05/col-property-1073741824/
http://www.progtown.com/topic523823-sectioning-ora14097.html