Re: Error while partition swp

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