Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669182] Sat, 07 April 2018 05:24 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All

We are doing the exchange partition on partitioned table with a Non-Partitioned table.
Even the non-partitioned table is created from the partitioned table(only required data by adding some where conditions).

but still I am getting the following exception

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.


I verified the structure of two tables in USER_TAB_COLUMNS, both are having same number of columns
with same data type.I don't have any UNUSED columns on both tables .

alter table ABC exchange partition ABC_PART_26JAN2018 with table NON_PART_TABLE;

Please help me to understand problem with exchange partition.

Thanks
SaiPradyumn
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669183 is a reply to message #669182] Sat, 07 April 2018 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66787
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use dbms_metadata.get_ddl for both tables and post the result.
Post the result of:
set linesize 120
col table_name format a20
col column_name format a20
col DATA_TYPE format a15
select table_name, COLUMN_NAME, 
       DATA_TYPE, DATA_TYPE_MOD, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, CHAR_USED,
       NULLABLE, HIDDEN_COLUMN, VIRTUAL_COLUMN
from user_tab_cols
where table_name in (<both tables>)
order by tabale_name, column_id
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669184 is a reply to message #669183] Sat, 07 April 2018 13:19 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Thank you very much for giving support to resolve the issue.
Here i am attaching the files for table structure & output of the queries.

Note: I manually compared the out put of the both queries and there is no differences between two outputs

Thanks
SaiPradyumn
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669185 is a reply to message #669184] Sat, 07 April 2018 15:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
Any chance NOT NULL column with DEFAULT value was added to one of the tables? Recent Oracle versions allow adding NOT NULL column with DEFAULT in one step regardless if table is empty or not by simply adding default value to metadata. Then, Oracle automatically substitutes NULL column value in fetched row with the default. However, such table metadata != same table metadata where column is defined as NOT NULL with DEFAULT at table create time. As a result we get ORA-14097:

DROP TABLE part_table PURGE
/
DROP TABLE no_part_table PURGE
/
CREATE TABLE part_table(
                        dt DATE NOT NULL
                       )
  PARTITION BY RANGE(dt)
    INTERVAL (INTERVAL '1' MONTH)
    (
     PARTITION p0 VALUES LESS THAN (DATE '2018-01-01')
    )
/
ALTER TABLE part_table
  ADD id NUMBER DEFAULT 0 NOT NULL
/
CREATE TABLE no_part_table(
                           dt DATE NOT NULL,
                           id NUMBER DEFAULT 0 NOT NULL
                          )
/
ALTER TABLE part_table
  EXCHANGE PARTITION p0
  WITH TABLE no_part_table
/
ALTER TABLE part_table
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


SQL>


And there will be no differences if you run Michel suggested script:

SQL> set linesize 120
SQL> col table_name format a20
SQL> col column_name format a20
SQL> col DATA_TYPE format a15
SQL> select table_name, COLUMN_NAME,
  2         DATA_TYPE, DATA_TYPE_MOD, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, CHAR_USED,
  3         NULLABLE, HIDDEN_COLUMN, VIRTUAL_COLUMN
  4  from user_tab_cols
  5  where table_name in ('PART_TABLE','NO_PART_TABLE')
  6  order by column_id,table_name
  7  /

TABLE_NAME           COLUMN_NAME          DATA_TYPE       DAT DATA_LENGTH DATA_PRECISION DATA_SCALE C N HID VIR
-------------------- -------------------- --------------- --- ----------- -------------- ---------- - - --- ---
NO_PART_TABLE        DT                   DATE                          7                             N NO  NO
PART_TABLE           DT                   DATE                          7                             N NO  NO
NO_PART_TABLE        ID                   NUMBER                       22                             N NO  NO
PART_TABLE           ID                   NUMBER                       22                             N NO  NO

SQL>

SY.



Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669199 is a reply to message #669185] Sun, 08 April 2018 06:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
I missed you are on 11G. Then, assuming issue I described is issue you are facing, fix is quite easy. But first let me show metadata difference which doesn't show up in DBA/ALL/USER_COLS. We need to query SYS.COL$ table:

select  o.name table_name,
        c.name column_name,
        to_char(c.property,'XXXXXXXXXX') property
  from  sys.obj$ o,
        sys.col$ c
  where o.owner# = uid
    and o.name in ('PART_TABLE','NO_PART_TABLE')
    and o.subname is null
    and c.obj# = o.obj#
    and c.name = 'ID'
/

TABLE_NAME      COLUMN_NAM PROPERTY
--------------- ---------- -----------
NO_PART_TABLE   ID                   0
PART_TABLE      ID            40000000

SQL> 

In 11G we can fix it by simply changing column to NULL and then back to NOT NULL:

SQL> select  banner
  2    from  v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> ALTER TABLE part_table
  2    EXCHANGE PARTITION p0
  3    WITH TABLE no_part_table
  4  /
ALTER TABLE part_table
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


SQL> select  o.name table_name,
  2          c.name column_name,
  3          to_char(c.property,'XXXXXXXXXX') property
  4    from  sys.obj$ o,
  5          sys.col$ c
  6    where o.owner# = uid
  7      and o.name in ('PART_TABLE','NO_PART_TABLE')
  8      and o.subname is null
  9      and c.obj# = o.obj#
 10      and c.name = 'ID'
 11  /

TABLE_NAME      COLUMN_NAM PROPERTY
--------------- ---------- -----------
NO_PART_TABLE   ID                   0
PART_TABLE      ID            40000000

SQL> alter table part_table
  2    modify id null
  3  /

Table altered.

SQL> alter table part_table
  2    modify id not null
  3  /

Table altered.

SQL> select  o.name table_name,
  2          c.name column_name,
  3          to_char(c.property,'XXXXXXXXXX') property
  4    from  sys.obj$ o,
  5          sys.col$ c
  6    where o.owner# = uid
  7      and o.name in ('PART_TABLE','NO_PART_TABLE')
  8      and o.subname is null
  9      and c.obj# = o.obj#
 10      and c.name = 'ID'
 11  /

TABLE_NAME      COLUMN_NAM PROPERTY
--------------- ---------- -----------
NO_PART_TABLE   ID                   0
PART_TABLE      ID                   0

SQL> 
SQL> ALTER TABLE part_table
  2    EXCHANGE PARTITION p0
  3    WITH TABLE no_part_table
  4  /

Table altered.

SQL> 

However, this fix doesn't work in 12C:

SQL> select  banner
  2    from  v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> ALTER TABLE part_table
  2    EXCHANGE PARTITION p0
  3    WITH TABLE no_part_table
  4  /
ALTER TABLE part_table
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


SQL> select  o.name table_name,
  2          c.name column_name,
  3          to_char(c.property,'XXXXXXXXXX') property
  4    from  sys.obj$ o,
  5          sys.col$ c
  6    where o.owner# = uid
  7      and o.name in ('PART_TABLE','NO_PART_TABLE')
  8      and o.subname is null
  9      and c.obj# = o.obj#
 10      and c.name = 'ID'
 11  /

TABLE_NAME      COLUMN_NAME     PROPERTY
--------------- --------------- -----------
NO_PART_TABLE   ID                        0
PART_TABLE      ID                 40000000

SQL> alter table part_table
  2    modify id null
  3  /

Table altered.

SQL> alter table part_table
  2    modify id not null
  3  /

Table altered.

SQL> select  o.name table_name,
  2          c.name column_name,
  3          to_char(c.property,'XXXXXXXXXX') property
  4    from  sys.obj$ o,
  5          sys.col$ c
  6    where o.owner# = uid
  7      and o.name in ('PART_TABLE','NO_PART_TABLE')
  8      and o.subname is null
  9      and c.obj# = o.obj#
 10      and c.name = 'ID'
 11  /

TABLE_NAME      COLUMN_NAME     PROPERTY
--------------- --------------- -----------
NO_PART_TABLE   ID                        0
PART_TABLE      ID                 40000000

SQL> ALTER TABLE part_table
  2    EXCHANGE PARTITION p0
  3    WITH TABLE no_part_table
  4  /
ALTER TABLE part_table
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


SQL> 

SY.
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669218 is a reply to message #669199] Mon, 09 April 2018 04:18 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Solomon Yakobson.

The solution which you had provided is working fine.
But here following are my concerns:

1) When this problem happens?Till now we had added so many columns to that partitioned table .
But this is the first time we are getting this type of problem.

2) Is there any alternate to avoid this?
Why because modifying the column to null and not null on huge partitioned table
defiantly impact the performance .

Instead of fixing the issue can we make sure that it shouldn't occur at all.

Is there any way to avoid that problem completely?

Thanks
Sai Pradyumn

Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669219 is a reply to message #669218] Mon, 09 April 2018 06:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
It happens when you add NOT NULL column with default value. Before 11G it wasn't allowed and you had to a) add column with default value b) update all existing rows and set newly added column to default value c) modify column to NOT NULL. Starting 11G Oracle does it in one step. It keeps NULL column value even though column is declared as NOT NULL and it also sets PROPERTY bit 40000000 which instructs to replace that NULL value with default value when fetching column data. However, that causes PROPERTY mismatch when column is created at table create time and exchange fails.

SY.
Re: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION [message #669271 is a reply to message #669219] Wed, 11 April 2018 09:14 Go to previous message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Solomon Yakobson
Previous Topic: Oracle 12C error tracing and debugging issue (merged 2)
Next Topic: 365 days always
Goto Forum:
  


Current Time: Thu Jan 23 02:50:45 CST 2020