Hi, We are using version 11.2.0.4 of oracle. We are seeing below error while doing partition exchange. Here the table TAB_PART is partition by range on a date column. And two b-tree composite indexes exists on it. No primary key/unique key exist on the table.And yes the stage table is compressed one but the table partition is uncompressed. I am unable to reproduce this on DEV.
ALTER TABLE TAB_PART EXCHANGE PARTITION PART1 WITH TABLE PART_STAGE INCLUDING INDEXES WITHOUT VALIDATION;
"ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION."
After the error as docs says we just immediately tried executing below statement to see exact difference in data type/column in both the main table and stage table, but not seeing anything. And also tried other way around(i.e. TAB_PART minus PART_STAGE) and still seeing zero difference.
select col#, name, type#, length, precision# from sys.col$
where obj# =(select object_id from dba_objects where object_name='PART_STAGE' and object_type='TABLE' and owner='USER1')
minus
select col#, name, type#, length, precision# from sys.col$
where obj# = (select object_id from dba_objects where object_name='TAB_PART' and object_type='TABLE' and owner='USER1');
Then i saw below document stating , even if the data type/columns matches exactly but the difference in constraints can raise such error. But then i see all the constraints those are in VALIDATED+ENABLED are exactly matching with the stage table. But i see some 3-4 constraints in the main table having status "DISABLED+NOT VALIDTED" and those are not in the stage table , as because we used CTAS for creating the stage table and then manually created the indexes. So these constraints with status "DISABLED+NOT VALIDTED" are not copied in the stage table but that's not exactly what the document states. It states if any enabled constraint is missed from stage table , that can cause such issue, which is not the case here. We have all the enabled constraints matching between the two tables. So i am unable to understand the cause of this error?
Again those 4 "DISABLED+NOT VALIDATED" constraints also not visible in DBA_CONSTRAINTS , but somehow i am seeing those in the toad when doing F4 on the table name, and are something like "SYS_C00XXXX". So want experts suggestion to understand the possible cause of this issue and to fix this?
ORA-14097: column type or size mismatch on Alter Table Exchange Partition (Doc ID 946427.1)