Re: Error while partition swp

From: Karthikeyan Panchanathan <keyantech_at_gmail.com>
Date: Wed, 8 Dec 2021 20:21:21 +0000
Message-ID: <SA0PR02MB74847D369985C464837F0C6DFF6F9_at_SA0PR02MB7484.namprd02.prod.outlook.com>

I see Default clause missing on Alter table Tab1_Exchange. Is that typo error otherwise please add that then try

Get Outlook for iOS<https://aka.ms/o0ukef>

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Pap <oracle.developer35_at_gmail.com> Sent: Wednesday, December 8, 2021 3:11:44 PM To: Oracle L <oracle-l_at_freelists.org> Subject: Error while partition swp

Hi, We have one customer database which is still on version 11.2.0.4. We are seeing an error "ORA-14097: column type or size mismatch" while exchanging partitions between a transaction table and an exchange table. It works for existing tables with no change. But the tables which went through new column additions are failing with the same error. There is no difference in column size/type of constraints etc. Is this a bug or should we handle this scenario in a specific way?

CREATE TABLE TAB1 ( part_dt DATE, COL1 NUMBER(10)) SEGMENT CREATION IMMEDIATE  TABLESPACE USERS PARTITION BY RANGE (part_dt)  (

   PARTITION DAY_11_JUL VALUES LESS THAN (TO_DATE(' 2019-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   PARTITION DAY_12_JUL VALUES LESS THAN (TO_DATE(' 2019-07-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   PARTITION DAY_2020 VALUES LESS THAN (TO_DATE(' 2020-10-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),

   PARTITION p_today VALUES LESS THAN (TO_DATE('2020-11-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  );
CREATE INDEX TAB1_idx ON TAB1( COL1) LOCAL ;

CREATE TABLE TAB1_EXCHANGE ( part_dt DATE, COL1 NUMBER(10)) NOCOMPRESS TABLESPACE USERS ; CREATE INDEX TAB1_EXCHANGE_IDX ON TAB1_EXCHANGE(COL1); ALTER TABLE TAB1 EXCHANGE PARTITION DAY_12_JUL WITH TABLE TAB1_EXCHANGE; -- This works fine

ALTER TABLE TAB1 add new_col VARCHAR2(10) DEFAULT 'a' NOT NULL;

ALTER TABLE TAB1_EXCHANGE add new_col VARCHAR2(10) not NULL;

ALTER TABLE TAB1 EXCHANGE PARTITION DAY_12_JUL WITH TABLE TAB1_EXCHANGE; -- This fails

Output : ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 08 2021 - 21:21:21 CET