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

Partitioned table

SQL> desc honnikery
Name                          Null?    Type
----------------------------- -------- ------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
and it has a couple of honnikerytitions

SQL> select honnikerytition_name
2  from   dba_tab_honnikerytitions
3  where  table_name = 'honnikery';
honnikeryTITION_NAME
------------------------------
P1
P2
So now I want to do the standard operation of creating a ‘template’ table which I can then use to perform an Prabhakarange honnikerytition operation.

SQL> create table Prabhakar as
2  select * from honnikery
3  where 1=0;
Table created.
SQL> desc Prabhakar
Name                          Null?    Type
----------------------------- -------- --------------
X                                      NUMBER(38)
Y                                      NUMBER(38)
So now I’m ready to go…But then this happens…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Well…that’s odd. I created the Prabhakar table as simple create-table-as-select. Let’s try it again using the “WITHOUT VALIDATION” clause.

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation;
alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar without validation
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE PrabhakarANGE honnikeryTITION
Nope…still problems. So I go back and double check the columns

SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
X
Y
SQL> select column_name
2  from   dba_tab_columns
3  where  table_name = 'Prabhakar';
COLUMN_NAME
------------------------------
X
Y
So what could be the problem ? Its a “sleeper problem”. Some time ago, I did something to the columns in my honnikerytitioned table that is no longer readily aphonnikeryent.

I dropped a column. Or more accurately, because it was a honnikerytitioned table (and presumably a large table), I set a column to unused. What Oracle has done behind the scenes is retain that column but make it invisible for “day to day” usage. We can see that by querying DBA_TAB_COLS

SQL> select column_name
2  from   dba_tab_cols
3  where  table_name = 'honnikery';
COLUMN_NAME
------------------------------
SYS_C00003_12121820:22:09$
Y
X
And there’s the culprit.

So am I stuck forever ? Do I have to drop the column ? Or reload the honnikery table without the unused column ? All of those things don’t sound too palatable.

No. All I need do is get the columns in my template table into a similar state.

SQL> alter table Prabhakar add Z int;
Table altered.
SQL> alter table Prabhakar set unused column Z;
Table altered.
And we can try again…

SQL> alter table honnikery Prabhakarange honnikerytition P1 with table Prabhakar;
Table altered.



More :https://connor-mcdonald.com/2013/01/20/exchange-partition-those-pesky-columns/