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@xxxxxxxxx>
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Pap <oracle.developer35@xxxxxxxxx>
*Sent:* Wednesday, December 8, 2021 3:11:44 PM
*To:* Oracle L <oracle-l@xxxxxxxxxxxxx>
*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
