Hi Tom,
We are converting oracle tables as partition tables.To do this we are using ORACLE Exchange Partition.
Database version 12C.
Steps we are following.
1.Original table --existing table
create table tab1(col1 integer,
col2 varchar2(100));2.Currently no date field, So adding a date column and populating using Default SYSDATE
alter table tab1 add col3 date default sysdate;
select col#,segcol#,name from sys.col$ where obj#=(select object_id from dba_objects where object_name='TAB1')
1 1 COL1
2 2 COL2
0 3 SYS_NC00003$
3 4 COL3
3.Creating interim table as similar to original table.
create table tab2
partition by range(col3)
interval(numtodsinterval(1,'DAY'))
(
partition PART_01 values less than (TO_DATE('01/09/2018', 'DD/MM/YYYY'))) ENABLE ROW MOVEMENT
as select * from tab1 where 1= 2;4.Using exchange partition to link data from original table.
ALTER TABLE tab2
EXCHANGE PARTITION PART_01 WITH TABLE tab1 WITHOUT VALIDATION ;
This is failing with below error
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
And when i look columns in user_tab_columns it is matching.But when i look at sys.col$, it is showing a different row with SYS_NC00003$.
This approach working fine in 11G, only getting issue in 12C.
Could you please advise how to get over this.
Thank You.
First up, there's no need to query sys.col$. Use *_tab_cols!
Anyway, the problem is due to the fast column default add for nullable columns in 12c. This creates the invisible SYS_* column. There are several other features which could lead to system-generated hidden columns. Setting a column unused being a key culprit.
So what to do?
Well this is addressed in bug 17741995. If you install patch 17741995 (included in 12.1.0.2), you can get around this by setting the event 14529:
create table tab1(
col1 integer,
col2 varchar2(100)
);
alter table tab1 add col3 date default sysdate;
select column_name, hidden_column
from user_tab_cols
where table_name = 'TAB1';
COLUMN_NAME HIDDEN_COLUMN
COL3 NO
SYS_NC00003$ YES
COL2 NO
COL1 NO
Alter session set events '14529 trace name context forever, level 512';
create table tab2
partition by range(col3)
interval(numtodsinterval(1,'DAY')) (
partition PART_01 values less than (to_date('01/09/2018', 'DD/MM/YYYY'))
) enable row movement
as
select * from tab1 where 1 = 2;
alter session set events '14529 trace name context off';
alter table tab2
exchange partition part_01 with table tab1 without validation ;
Table TAB2 altered.See MOS note 2240439.1.
If you're on 12.2, there is another possibility: create the table with the "for exchange with" parameter. Then alter the new table to be partitioned:
drop table tab2 cascade constraints purge;
create table tab2 for exchange with table tab1;
alter table tab2 modify partition by range(col3)
interval(numtodsinterval(1,'DAY')) (
partition PART_01 values less than (to_date('01/09/2018', 'DD/MM/YYYY'))
);
alter table tab2
exchange partition part_01 with table tab1 without validation ;
Table TAB2 altered.