CODEWITHSUNDEEP
partitioningoracle12c
Ricardo Mota
Ricardo Mota

Reputation: 1214

Partition exchange column type or size mismatch (ORA-14097)

I'm trying to do a exchange partition on a database and I'm having the following error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

The script that does this was already created and it was running as expected on an Oracle 11g database. As soon as I've updated to 12c I've got this problem. This is how I'm doing the partition exchange:

-- The new partitioned table.
CREATE TABLE NEW_TABLE
(
  id             NUMBER(18) NOT NULL,
  message        VARCHAR2(4000) NOT NULL,
  details        VARCHAR2(4000),
  partition_time TIMESTAMP(6) DEFAULT to_timestamp('01-01-2016','dd-mm-yyyy HH24:MI') NULL
) NOCOMPRESS LOGGING 
  PARTITION BY RANGE (partition_time) INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
   (PARTITION initial VALUES LESS THAN (to_timestamp('01-01-2016','dd-mm-   yyyy HH24:MI')));

-- The old table.
CREATE TABLE OLD_TABLE
 (
  id            NUMBER(18,0) NOT NULL,
  message         VARCHAR2(4000 byte) NOT NULL,
  details           VARCHAR2(4000),
);

-- Add the column that does not exist on the old table (keep the same columns).
ALTER TABLE OLD_TABLE ADD partition_time TIMESTAMP(6) DEFAULT to_timestamp('01-01-2016','dd-mm-yyyy HH24:MI') NULL;

ALTER TABLE NEW_TABLE
EXCHANGE PARTITION INITIAL
WITH TABLE OLD_TABLE
WITHOUT VALIDATION;

(...)

Now, once again, on Oracle 11g this was working perfectly. On Oracle 12c I've got the error explained above. I've did some research and I've seen people talk about INVISIBLE columns. Well, I've recreated the OLD_TABLE so I think there will be no invisible columns.

EDIT:

I've realized that on Oracle 12c when I try to alter the table to create a new column another invisible column is created (named SYS_NC00011$). This is why the partition exchange is not working. My question now is why is this happening and what is the best way to "remove this column" ? Already tryied to drop unused columns with no success.

Thank you guys!

Upvotes: 3

Views: 18721

Answers (4)

crowne
crowne

Reputation: 8534

Thanks to wolφi for highlighting the hidden columns and pointing me in the right direction.
I confirmed the hidden columns with the query below:

SELECT * FROM SYS.dba_tab_cols

I then recreated my staging table including the system generated column names with matching types and in the same order according to INTERNAL_COLUMN_ID.

The partition exchange still failed because the new columns were showing as USER_GENERATED='YES'

The final fix was to mark the columns as unused:

ALTER TABLE STAGING_TABLE
set unused ("SYS_C00006_16092719:09:49$"
       ,"SYS_C00007_16092719:10:34$"  
       ,"SYS_C00008_16092719:06:48$"
       ,"SYS_C00009_16092719:07:00$"
       ,"SYS_C00010_16092719:07:10$"
       ,"SYS_C00011_16092719:08:15$"
       ,"SYS_C00012_16092719:08:59$"  );

After this the partition exchange worked.

Upvotes: 1

pahariayogi
pahariayogi

Reputation: 1163

Another reilable solution without compromising anything would be to create/rebuild OLD_TABLE (non-partitioned) using "..FOR EXCHANGE.." clause. It's available only from Oracle version 12.2 onwards.

CREATE TABLE OLD_TABLE **FOR EXCHANGE** WITH TABLE NEW_TABLE;

It's not clear from your description if the OLD_TABLE is empty or has data in your case. If you have data, you can populate data in it using

INSERT INTO OLD_TABLE SELECT * FROM <old backup table>;

This avoids ORA-14097 (or ORA-00932 in some cases) during the 'exchange partition' get the job done seamlessly. Oracle could sense issues with "exchange partition" soon after introducing DDL optimisation related to DEFAULT column attribute and hence introduced "..FOR EXCHANGE.." version of CTAS operation from 12.2 onwards.

Upvotes: 2

wolφi
wolφi

Reputation: 8361

We ran recently into the same error. Similar to your case, the error was triggered by a hidden column (and it wasn't even easter ;-). In our case the hidden column was caused by a ALTER TABLE xxx DROP COLUMN yyy of a compressed table.

In your case, it seems very likely that the hidden column is created by the ALTER TABLE xxx ADD COLUMN yyy NULL. As the article DDL Optimization in Oracle Database 12c and this answer explains, adding a NULL column does some data dictionary magic and adds a hidden column to track if the new column has been written to for each row.

CREATE TABLE old_table (
  id       NUMBER(18,0)        NOT NULL,
  message  VARCHAR2(4000 BYTE) NOT NULL,
  details  VARCHAR2(4000)
 );

ALTER TABLE old_table ADD partition_time TIMESTAMP(6) 
   DEFAULT to_timestamp('01-01-2016','dd-mm-yyyy HH24:MI') NULL;

SELECT * FROM user_tab_cols WHERE table_name='OLD_TABLE';

ID             NUMBER
MESSAGE        VARCHAR2 
DETAILS        VARCHAR2
SYS_NC00004$   RAW
PARTITION_TIME TIMESTAMP(6)

So, to fix your case, either recreate the table including the column partition_time:

CREATE TABLE old_table (
  id             NUMBER(18,0)        NOT NULL,
  message        VARCHAR2(4000 BYTE) NOT NULL,
  details        VARCHAR2(4000),
  partition_time TIMESTAMP(6) DEFAULT DATE '2016-01-01'
 );

or add the column without a DEFAULT:

ALTER TABLE OLD_TABLE ADD partition_time TIMESTAMP(6) NULL;

or disable the new feature (Doc Id 2277937.1):

ALTER SESSION SET "_add_col_optim_enabled"=FALSE ;
ALTER TABLE old_table ADD partition_time TIMESTAMP(6) 
   DEFAULT to_timestamp('01-01-2016','dd-mm-yyyy HH24:MI') NULL;

SELECT * FROM user_tab_cols WHERE table_name='OLD_TABLE';

ID             NUMBER
MESSAGE        VARCHAR2 
DETAILS        VARCHAR2
PARTITION_TIME TIMESTAMP(6)

I haven't found a way yet to rebuild the table to get rid of the hidden column. ALTER TABLE MOVE does not help, only CREATE TABLE AS SELECT does.

Upvotes: 3

BobC
BobC

Reputation: 4416

The most obvious one is that NEW_TABLE has a PARTITION_TIME column, while OLD_TABLE does not. The other things to check, that might be an issue

  • NEW_TABLE.ID is NUMBER(18,0), while OLD_TABLE.ID is NUMBER(18)
  • OLD_TABLE.MESSAGE is VARCHAR2(4000 byte). You should check your
    length semantics, since if they are defined as CHAR, then
    NEW_TABLE.message would be VARCHAR2(4000 char).

Upvotes: 0

Related Questions