SQL & PL/SQL

Hi All,

I am trying to do exchange partition but getting the below error.

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

Can't paste the actual code here, but it's correct as it works in some of the environments and schemas but not in other environments and schemas.
Have checked all the indexes, constraints and columns definitions of the two tables. I am using DBMS_METADATA.GET_DDL only to generate the staging tables. I have even checked the UNUSED columns too but to no avail, it still fails.

Only resolution is to re-create the main table, then create staging table based on the main table and then do the exchange partition.

Just can't understand how is this possible, when I recreate the table as the replica of the main table, exchange partition works but wouldn't work on the existing table.

I know it's difficult to answer without seeing the actual code, but just wanted to check has anyone faced issue like this before? Is there any bug in ORACLE? Went through many posts over internet and people have faced this issue but couldn't get a concrete answer.

Please help

Edited by: Ishan on Jul 18, 2012 1:32 AM