Re: Error while partition swp

And just curious , is there a feature on 19C to create the Exchange table dynamically from the main table without manually doing it from its DDL or getting it manually fetched from Dbms_metadata. And thus it will avoid such property mismatch error.?

On Thu, Dec 9, 2021 at 6:09 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Thanks a lot. It was a mistake. It's working after adding that.
>
> On Thu, Dec 9, 2021 at 1:51 AM Karthikeyan Panchanathan <
> keyantech_at_gmail.com> wrote:
>
>> 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