Re: Error while partition swp

There is create table for exchange syntax in 12.2, it makes this task trivial.

Thanks,
Andrew

On Thu, 9 Dec 2021 at 12:45, Pap <oracle.developer35_at_gmail.com> wrote:

> 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