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@xxxxxxxxx> 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@xxxxxxxxx> 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@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
