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

I ran into the ORA-14097 while exchanging partition. Can anyone share me some light?

I have the following source_tbl (non-partitioned) table and is intended to partition it using the column "VALID_PERIOD_END"

CREATE TABLE source_tbl
   (    INVOICE_ID NUMBER(15,0) NOT NULL ENABLE, 
    LATEST_FLAG_NAME VARCHAR2(3000), 
    STD_HASH **RAW**(1000), 
    VALID_PERIOD_START TIMESTAMP (6), 
    VALID_PERIOD_END **TIMESTAMP** (6), 
    OVERSEAS NUMBER, 
   .. <another 20 number columns)
    VIP_NO NUMBER
   ) partition by range(VALID_PERIOD_END)
nologging;

This table now have 5M rows and I want to partition it by VALID_PERIOD_END so that if it is '9999-12-31 23:59:59' (current) will be in one partition while the rest with be in another partition

I have created a second table called TEMP_tbl

CREATE TABLE TEMP_tbl
   (    INVOICE_ID NUMBER(15,0) NOT NULL ENABLE, 
    LATEST_FLAG_NAME VARCHAR2(3000), 
    STD_HASH **RAW**(1000), 
    VALID_PERIOD_START TIMESTAMP (6), 
    VALID_PERIOD_END **TIMESTAMP** (6), 
    OVERSEAS NUMBER, 
   .. <another 20 number columns)
    VIP_NO NUMBER
   )partition by range(VALID_PERIOD_END)
(partition p1 values less than(maxvalue)) nologging;

The TEMP_tbl have exactly the same data structure as source_tbl as the script was driven using dbms_metadata.get_ddl

I have executed gather table status without any error returned

EXEC DBMS_STATS.gather_table_stats(USER, upper('source_tbl'), cascade => TRUE);

However, when I try to execute the following exchange partition statement, I have the error above

alter table TEMP_tbl
          exchange partition p1
          with table source_tbl
          without validation
          update global indexes
;

I have checked "user_tab_cols" and I confirm there is no hidden column from the source_tbl. Would it be because of the raw column from my table?

Thanks in advance!

Oracle 12.2 introduced two new partitioning features that will help you a great deal here.

1. A new ALTER TABLE MODIFY PARTITION BY DDL was introduced that allows conversion of a non-partitioned table to a partitioned table. This operation will copy the data from the existing non-partitioned table into the new table partitions, so it can be long-running. You may specify the ONLINE keyword to do the operation in online mode, which means that DML operations on the table will be allowed while the ALTER TABLE is running. For example:

    ALTER TABLE source_tbl
      MODIFY PARTITION BY RANGE(VALID_PERIOD_END)
      (partition p1 values less than (timestamp '9999-12-31 23:59:59'),
       partition p2 values less than (maxvalue))
      ONLINE;

2. To help generally with the sort of EXCHANGE PARTITION problem you are facing, the FOR EXCHANGE WITH TABLE clause was introduced in CREATE TABLE. This is specifically intended for matching physical columns exactly when creating a new table that will be exchanged with an existing table. Optionally FOR EXCHANGE WITH TABLE can be used along with PARTITION BY to create a partitioned table that can be exchanged with the source table. For example:

    CREATE TABLE TEMP_tbl
      PARTITION BY RANGE(VALID_PERIOD_END)
      (partition p1 values less than(maxvalue))
      FOR EXCHANGE WITH TABLE source_tbl;

Here is a blog article that describes both of these partitioning enhancements. And here is another blog article that specifically talks using CREATE TABLE ... FOR EXCHANGE WITH TABLE to solve EXCHANGE PARTITION errors.

You didn't mention which version of Oracle you are using, so perhaps you are still running 11g. In this case you probably need to drill down into USER_TAB_COLS to see what the difference is between the two tables. You mentioned that you already checked for hidden columns (which is good) but other mis-matches could occur.

One thing to keep in mind is that NULLABLE column attributes must match between the two tables. If one table has primary key constraint and the other one doesn't, the column may be non-nullable in the table with primary key and nullable in the other table, which will cause ORA-14097.

If that doesn't explain the problem you can also check SEGMENT_COLUMN_ID ordering, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, and DATA_PRECISION. Since you used dbms_metadata.get_ddl to create your table these things should match, but there must be some difference otherwise you would not get the error.

The RAW(1000) column should not be a problem for EXCHANGE PARTITION.