Re: Error while partition swp

Thank You So much Andy!!

On Thu, Dec 9, 2021 at 6:41 PM Andy Sayer <andysayer_at_gmail.com> wrote:

> 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