ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
I was moving data from my main table to history table using the below command.
alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES;
But I was getting the below error.
SQL> alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES;
alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
When I checked the tables description all the columns are same in main and history table.
SQL> desc parent
Name Null? Type
—————————————– ——– —————-
ID NOT NULL NUMBER(38)
NAME VARCHAR2(2000)
DOJ DATE
SQL> desc parent_history
Name Null? Type
—————————————– ——– —————-
ID NUMBER(38)
NAME VARCHAR2(2000)
DOJ DATE
I have executed the below query and found that there is a hidden unused column in main table.
SQL> select col#, name , type#
from sys.col$
where obj# in
(select object_id from dba_objects where object_name = ‘PARENT’)
order by col#; 2 3 4 5
COL# NAME
———- ——————————
0 SYS_C00004_16110810:17:00$
1 ID
2 NAME
3 DOJ
Previously I have created the table using the “DBMS_REDEFINITION” which caused to create the hidden unused column.
Now the solution is drop this unused column and try again
1) Drop the hidden columns
SQL> ALTER TABLE PARENT DROP UNUSED COLUMNS;
Table altered.
SQL> alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES;
Table altered.
alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES;
But I was getting the below error.
SQL> alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES;
alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
When I checked the tables description all the columns are same in main and history table.
SQL> desc parent
Name Null? Type
—————————————– ——– —————-
ID NOT NULL NUMBER(38)
NAME VARCHAR2(2000)
DOJ DATE
SQL> desc parent_history
Name Null? Type
—————————————– ——– —————-
ID NUMBER(38)
NAME VARCHAR2(2000)
DOJ DATE
I have executed the below query and found that there is a hidden unused column in main table.
SQL> select col#, name , type#
from sys.col$
where obj# in
(select object_id from dba_objects where object_name = ‘PARENT’)
order by col#; 2 3 4 5
COL# NAME
———- ——————————
0 SYS_C00004_16110810:17:00$
1 ID
2 NAME
3 DOJ
Previously I have created the table using the “DBMS_REDEFINITION” which caused to create the hidden unused column.
Now the solution is drop this unused column and try again
1) Drop the hidden columns
SQL> ALTER TABLE PARENT DROP UNUSED COLUMNS;
Table altered.
SQL> alter table parent exchange partition SYS_P27396 with table parent_history UPDATE GLOBAL INDEXES;
Table altered.
Viswamitra's blog 