Hi,
I'm having this error in Oracle and I could not really understand if there is a solution.
Oracle version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
I create a partitioned table with 3 columns and 1 partition (just as example):
create table part_table (
col1 number
,col2 number
,col3 number
)
partition by list(col1)
(partition p_0001 values (1))
;
If I check the number of rows I can see that I have 3 rows:
select column_name, data_type, data_length, hidden_column, column_id
,internal_column_id
from all_tab_cols
where table_name = 'PART_TABLE'
order by internal_column_id
;
COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN COLUMN_ID INTERNAL_COLUMN_ID
-------------------- --------------- ----------- --------------- ---------- ------------------
COL1 NUMBER 22 NO 1 1
COL2 NUMBER 22 NO 2 2
COL3 NUMBER 22 NO 3 3
I add one new column with default value 1:
alter table part_table
add col4 number default 1;
If I check again the column list:
select column_name, data_type, data_length, hidden_column, column_id
,internal_column_id
from all_tab_cols
where table_name = 'PART_TABLE'
order by internal_column_id
;
COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN COLUMN_ID INTERNAL_COLUMN_ID
-------------------- --------------- ----------- --------------- ---------- ------------------
COL1 NUMBER 22 NO 1 1
COL2 NUMBER 22 NO 2 2
COL3 NUMBER 22 NO 3 3
SYS_NC00004$ RAW 126 YES 4
COL4 NUMBER 22 NO 4 5
A new hidden column SYS_NC0004$ has been added to the table. This has something to do with default value of the new column added.
If I try to create a table for exchanging the partition this does not work:
create table xchg_table as
select *
from part_table
where 1=0
;
alter table part_table exchange partition p_0001
with table xchg_table without validation;
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
The problem lies exactly in that hidden column and apparently there is no way to create a table that reflects exactly the structure of the partitioned table.
Unless we follow exactly the same steps: create the table with 3 columns and add the fourth column later on.
Do you know if there is a way to work around this?
Regards,
Alberto