Purpose
<Internal_Only* ***
To provide information about various frequently experienced cases that can lead to ORA-14097.
Scope
Useful to DBA's or anyone who are working with partitioned tables e.g. doing partition exchange load.
Details
Disclaimer:
In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
ALTER TABLE EXCHANGE PARTITION converts a partition (or subpartition) into a non-partitioned table and the non-partitioned table into the partition (or subpartition) by exchanging the metadata associated with their data segments.
This operation is often used to load data to a partition by creating a non partition table with that looks exactly like a single partition, including the same indexes and constraints, if any. Data can be loaded into the separate table, build indexes and implement constraints on the separate table, without impacting the table users query. Then perform the partition exchange load, which is a very low-impact transaction compared to the data load, and often used in data warehouse environments.
Refer the following note for more details:
Exchange Partition - Examples (Doc ID 132989.1)
In this note we will discuss various situations that can lead to ORA-14097 and the possible solutions.
Details of the error message:
Error: ORA-14097
Text: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
---------------------------------------------------------------------------
Cause: The corresponding columns in the tables specified in the ALTER TABLE EXCHANGE PARTITION are of different type or size
Action: Ensure that the two tables have the same number of columns with the same type and size
CASE 1:Partitioned and Non-Partitioned tables are not structurally identical
Consider:
ALTER TABLE <partitionTable> EXCHANGE PARITITON <partition> WITH TABLE <table>;
The following SQL will identify all non-matching columns:
SQL>select a.column_id "COLUMN_ID", a.table_name "TABLE_NAME",
a.column_name, a.data_type, a.data_length
from user_tab_columns a, user_tab_columns b
where a.column_id (+) = b.column_id
and (a.data_type != b.data_type
or a.data_length != b.data_length)
and a.table_name = upper('<partitionTable>')
and b.table_name = upper('<table>')
union
select b.column_id "COLUMN_ID", b.table_name "TABLE_NAME",
b.column_name, b.data_type, b.data_length
from user_tab_columns a, user_tab_columns b
where b.column_id (+) = a.column_id
and (a.data_type != b.data_type
or a.data_length != b.data_length)
and a.table_name = upper('<partitionTable>')
and b.table_name = upper('<table>')
order by table_name, column_id;
Let us take an example to illustrate this case.
EXAMPLE:
SQL> -- Create the partitioned table - ptab.
SQL> create table ptab (
2 c1 integer,
3 c2 varchar2(20))
4 partition by range (c1)
5 (partition ptab_10 values less than (10),
6 partition ptab_20 values less than (20),
7 partition ptab_max values less than (maxvalue));
Table created.
SQL> insert into ptab values (5,'aaa');
1 row created.
SQL> select column_id, column_name, data_type, data_length
2 from user_tab_columns
3 where table_name = 'PTAB'
4 order by column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH
-----------------------------------------------------------------------
1 C1 NUMBER 22
2 C2 VARCHAR2 20
SQL> -- Create non-partitioned table - etab.
SQL> create table etab (
2 c2 varchar2(20),
3 c1 integer);
Table created.
SQL> insert into etab values ('bbb',15);
1 row created.
SQL> select column_id, column_name, data_type, data_length
2 from user_tab_columns
3 where table_name = 'ETAB'
4 order by column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH
-----------------------------------------------------------------------
1 C2 VARCHAR2 20
2 C1 NUMBER 22
SQL> -- Atempt to exchange the partition with table.
SQL> alter table ptab
2 exchange partition ptab_20 with table etab;
alter table ptab
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SQL> -- Examine structural differences.
SQL> select a.column_id "COLUMN_ID", a.table_name "TABLE_NAME",
2 a.column_name, a.data_type, a.data_length
3 from user_tab_columns a, user_tab_columns b
4 where a.column_id (+) = b.column_id
5 and (a.data_type != b.data_type
6 or a.data_length != b.data_length)
7 and a.table_name = upper('PTAB')
8 and b.table_name = upper('ETAB')
9 union
10 select b.column_id "COLUMN_ID", b.table_name "TABLE_NAME",
11 b.column_name, b.data_type, b.data_length
12 from user_tab_columns a, user_tab_columns b
13 where b.column_id (+) = a.column_id
14 and (a.data_type != b.data_type
15 or a.data_length != b.data_length)
16 and a.table_name = upper('PTAB')
17 and b.table_name = upper('ETAB')
18 order by table_name, column_id;
COLUMN_ID TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH
----------------------------------------------------------------------
1 ETAB C2 VARCHAR2 20
2 ETAB C1 NUMBER 22
1 PTAB C1 NUMBER 22
2 PTAB C2 VARCHAR2 20
In the above example the ALTER TABLE EXCHANGE PARTITION statement fails because the column datatypes are not identical per column identifier (column_id) even though the datatypes match based on column name (column_name).
SOLUTION:
Since it is not possible to change the column identifier of a column - the column order must be changed by creating a new non-partitioned table:
1. CREATE TABLE AS SELECT (CTAS) to build a new non-partitioned table that matches the column order. Based on the size of the non-partitioned table - this could be an expensive operation (time and space considered).CTAS is an already optimized operation, if you use INSERT ... SELECT, then you may want to do it with direct path.
2. Consider other options other than ALTER TABLE EXCHANGE PARTITION that will accomplish the business need.
The following will demonstrate the CTAS approach.
SQL> -- Create duplicate of non-partitioned table with compatible column order
SQL> create table etab_duplicate
2 as select c1, c2 from etab;
Table created.
SQL> -- Exchange the partition and the table.
SQL> alter table ptab
2 exchange partition ptab_20 with table etab_duplicate;
Table altered.
CASE 2:Presence of UNUSED columns
Oracle 8i & above allows columns to be logically (UNUSED) and physically (DROP) dropped from a table. UNUSED columns are hidden from the queries and will not be accessible through any of the data dictionary views. [ALL|DBA|USER]_UNUSED_COL_TABS lists the number of unused columns per table.
Although UNUSED column(s) have a column identifier (sys.col$.col#) of zero - they internally maintain their original position based identifier. Thus,if both the partitioned table and non-partitioned table have UNUSED columns and these columns match (datatype and size) per original column identifier - then the ALTER TABLE EXCHANGE PARTITION will succeed. If the partitioned table has UNUSED columns, it will probably not be possible to make the non-partition table structurally equivalent by 'adding' unused columns. The only exception is if the UNUSED columns in the partitioned table have the maximum column identifer(s) and the datatype and size are known. Since it is not possible to reverse the UNUSED statement (point-in-time recovery is required to undo action) - the solution is to drop the unused columns. This may be a time consuming operation based on the number of rows - but the exchange will succeed.
Let us take an example.
EXAMPLE:
SQL> create table ptab (
2 c1 integer,
3 c2 varchar2(20))
4 partition by range (c1)
5 (partition ptab_10 values less than (10),
6 partition ptab_20 values less than (20),
7 partition ptab_max values less than (maxvalue));
Table created.
SQL> create table etab (
2 c1 integer,
3 c2 varchar2(20),
4 c3 integer);
Table created.
SQL> alter table etab set unused column c3;
Table altered.
SQL> select column_id, column_name, data_type, data_length
2 from user_tab_columns
3 where table_name = 'ETAB'
4 order by column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH
-----------------------------------------------------------------------
1 C1 NUMBER 22
2 C2 VARCHAR2 20
SQL> -- SYS.COL$ is the only way to determine position of UNUSED columns
SQL> select col#, name
2 from sys.col$
3 where obj# in
4 (select object_id from user_objects where object_name = 'ETAB');
COL# NAME
---------- ------------------------------
1 C1
2 C2
0 SYS_C00003_99062517:44:38$
SQL> alter table ptab
2 exchange partition ptab_20 with table etab;
exchange partition ptab_20 with table etab
*
ERROR at line 2:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SQL> -- Since column is UNUSED - it is not visible from data dictionary
SQL> select a.column_id "COLUMN_ID", a.table_name "TABLE_NAME",
2 a.column_name, a.data_type, a.data_length
3 from user_tab_columns a, user_tab_columns b
4 where a.column_id (+) = b.column_id
5 and (a.data_type != b.data_type
6 or a.data_length != b.data_length)
7 and a.table_name = upper('PTAB')
8 and b.table_name = upper('ETAB')
9 union
10 select b.column_id "COLUMN_ID", b.table_name "TABLE_NAME",
11 b.column_name, b.data_type, b.data_length
12 from user_tab_columns a, user_tab_columns b
13 where b.column_id (+) = a.column_id
14 and (a.data_type != b.data_type
15 or a.data_length != b.data_length)
16 and a.table_name = upper('PTAB')
17 and b.table_name = upper('ETAB')
18 order by table_name, column_id;
no rows selected
SOLUTION:
SQL> -- Determine existance of unused columns in non-partitioned table.
SQL> select * from user_unused_col_tabs;
TABLE_NAME COUNT
------------------------------ ----------
ETAB 1
SQL> -- Drop unused columns => tables structurally equivalent
SQL> alter table etab drop unused columns;
Table altered.
SQL> -- Exchange
SQL> alter table ptab
2 exchange partition ptab_20 with table etab;
Table altered.
CASE 3:Presence of Function Based Indexes
If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail. Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference.
EXAMPLE:
SQL> create table etab (
2 c1 integer,
3 c2 varchar2(20));
Table created.
SQL> create index etab_fidx on etab (upper(c2));
Table altered.
SQL> select column_id, column_name, data_type, data_length
2 from user_tab_columns
3 where table_name = 'ETAB'
4 order by column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH
-----------------------------------------------------------------------
1 C1 NUMBER 22
2 C2 VARCHAR2 20
SQL> -- SYS.COL$ is the only way to find the column mapped to the function Based index
SQL> select col#, name
2 from sys.col$
3 where obj# in
4 (select object_id from user_objects where object_name = 'ETAB');
COL# NAME
---------- ------------------------------
1 C1
2 C2
0 SYS_NC00004$
SQL> alter table ptab
2 exchange partition ptab_20 with table etab;
exchange partition ptab_20 with table etab
*
ERROR at line 2:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SOLUTION:
The solution is to drop the function based index before the exchange and recreate it after the exchange . This may be a time consuming operation based on the size of the table - but the exchange will succeed.
CASE 4: When trying to exchange any partitions of existing tables with a new table created using "CREATE TABLE AS SELECT" command from the original partition table an ora-14097 error is encountered
If there are no UNUSED columns or function based indexes,then this is Expected Behavior.
The partitioned table column is not originally defined as NOT NULL. However, this column is defined as the primary key by adding a primary key constraint to the table. By virtue of this constraint, ORACLE will ennforce this column to be NOT NULL. However, a CTAS statement is not expected to copy primary key constraints from the source table and therefore, the column in the new table will be NULL.
SOLUTION:
Do not use CTAS to create the new table partition for the exchange command, or apply the same DDL on the exchange table.
For more details see the following:
Document 315347.1 ORA-14097 During ALTER TABLE EXCHANGE PARTITION
Document 3074782.1 ORA-14097 DURING EXCHANGE PARTITION WITH MAX_STRING_SIZE = EXTENDED
Document 2730522.1 Partition Exchange Fails With ORA-14097
CASE 5: ALTER TABLE EXCHANGE PARTITION fails even though both tables have the same column names and data types when a column has been added via alter table
EXAMPLE:
SQL> CREATE TABLE exchtest (c1 CHAR(9) NOT NULL) PARTITION BY RANGE (c1) (PARTITION exchtest_00 VALUES LESS THAN (MAXVALUE));
Table created.
SQL> ALTER TABLE exchtest ADD (c2 NUMBER DEFAULT 0 NOT NULL);
Table altered.
SQL> CREATE TABLE exchtest_reorg_2 AS SELECT * FROM exchtest WHERE 1=0;
Table created.
SQL> ALTER TABLE exchtest EXCHANGE PARTITION exchtest_00 WITH TABLE exchtest_reorg_2;
ALTER TABLE exchtest EXCHANGE PARTITION exchtest_00 WITH TABLE exchtest_reorg_2
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
SOLUTION:
Use the following steps when creating the table for the exchange using create table as select.
SQL > CREATE TABLE exchtest (c1 CHAR(9) NOT NULL) PARTITION BY RANGE (c1) (PARTITION exchtest_00 VALUES LESS THAN (MAXVALUE));
Table created.
SQL > ALTER TABLE exchtest ADD (c2 NUMBER DEFAULT 0 NOT NULL);
Table altered.
SQL> alter session set events '14529 trace name context forever, level 2';
Session altered.
SQL > CREATE TABLE exchtest_reorg_2 AS SELECT * FROM exchtest WHERE 1=0;
Table created.
SQL> alter session set events '14529 trace name context off';
Session altered.
SQL > ALTER TABLE exchtest EXCHANGE PARTITION exchtest_00 WITH TABLE exchtest_reorg_2;
Table altered.
Note that event 14529 is set at level 2 and this carries over the needed column property so that the exchange can succeed.
The event needs to be turned on for the scope of the CTAS only.
CASE 6: When trying to exchange any partitions of existing tables with a table from the original partition table an ora-14097 error is encountered. The VALIDATED status of the constraint on any of the table has status as "NOT VALIDATED"
There are no UNUSED columns or function based indexes.the following queries does return no rows:
SQL>select col#, intcol#, name, type#, length, precision# from sys.col$
where obj# =(select object_id from dba_objects where object_name='object A' and object_type='TABLE')
minus
select col#, intcol#, name, type#, length, precision# from sys.col$
where obj# = (select object_id from dba_objects where object_name='object B' and object_type='TABLE');
SQL>select col#, intcol#, name, type#, length from sys.col$
where obj# = (select object_id from dba_objects where object_name='object B' and object_type='TABLE')
minus
select col#, intcol#, name, type#, length from sys.col$
where obj# =(select object_id from dba_objects where object_name='object A' and object_type='TABLE');
Expected behavior.
The constraint of the exchange table is NOT VALIDATED. So, that needs to be VALIDATED same as the original partitioned table.
SOLUTION:
SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,VALIDATED from dba_constraints where TABLE_NAME='LOAN_INT_AMT';
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS VALIDATED
------------------------------ ------------------------------ - ------------------------------ -------- -------------
SAMIR SYS_C0011167 C LOAN_INT_AMT ENABLED NOT VALIDATED
SAMIR SYS_C0011166 C LOAN_INT_AMT ENABLED VALIDATED
SAMIR SYS_C0011165 C LOAN_INT_AMT ENABLED VALIDATED
SAMIR SYS_C0011164 C LOAN_INT_AMT ENABLED VALIDATED
SAMIR PK_LIAM P LOAN_INT_AMT ENABLED VALIDATED
TEST SYS_C0010070 C LOAN_INT_AMT ENABLED VALIDATED
TEST SYS_C0010069 C LOAN_INT_AMT ENABLED VALIDATED
TEST SYS_C0010068 C LOAN_INT_AMT ENABLED VALIDATED
TEST SYS_C0010067 C LOAN_INT_AMT ENABLED VALIDATED
TEST PK_LIAM P LOAN_INT_AMT ENABLED VALIDATED
SQL> alter table samir.loan_int_amt enable validate constraint SYS_C0011167;
Table altered.
SQL> alter table test.loan_int_amt exchange partition p_082009 with table samir.loan_int_amt;
Table altered.
CASE 7: ORA-14097 can occur on EXCHANGE PARTITION when all constraints are disabled
ORA-14097 on exchange partition when you disable all the constraints. Also you may see -1 set to NULL$ property in col$ that can be this issue.
CASE 8:If the table has a user-defined type, and that type is altered, the exchange will fail with ORA-14097
Example:
create or replace type type1 as object (data1 number(2));
/
create table table1(column1 number(5), column2 type1)
partition by range(column1)
(partition p1 values less than(10));
-- there must be a pk for redefinition
ALTER TABLE table1 ADD (CONSTRAINT table1_pk PRIMARY KEY (column1));
alter type type1 modify attribute (data1 number(4)) cascade;
create table newtable (column1 number(5), column2 type1)
partition by range(column1)
(partition p1 values less than(10));
create table table1_exchange2 as select * from table1 where 1=0;
-- exchange will fail
alter table table1 exchange partition P1 with table table1_exchange2;
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
-- now redefine table 1
-- create interim table
create table newtable create table table1(column1 number(5), column2 type1)
partition by range(column1)
(partition p1 values less than(10));
EXEC Dbms_Redefinition.can_redef_table(USER, 'table1');
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'table1',
int_table => 'newtable');
END;
/
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'table1',
int_table => 'newtable');
END;
/
SQL> alter table table1 exchange partition P1 with table table1_exchange2;
Table altered.
---------相关问题
Please run the following SQL in the same environment where the error occurred.
a. SELECT TABLE_NAME, DROPPABLE, SUBSTR(EXTENSION_NAME,1,35) EXTENSION_NAME, EXTENSION
from dba_stat_extensions WHERE table_name like 'GL_%';
b. SELECT column_name, virtual_column, segment_column_id,internal_column_id
from all_tab_cols
where table_name like 'GL_%' and virtual_column = 'YES';---
------------------
Symptoms
On : 19.6.0.0.0 version, RDBMS
Partition exchange fails with following error message:
Error:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
All usual documented checks show no mismatch or cause for error.
Changes
Cause
Mismatch in the internal column id of the exchange table and original table
The order of the columns as per the column_id is different from the internal_column_id in the two tables. This would create a mismatch.
It can be checked by running the following query for both the tables separately and comparing the results.
SQL> col column_name form a30
SQL> select column_id, column_name, internal_column_id, data_type, data_length
from all_tab_cols
where table_name = '&&tablename'
order by column_id;
How To Exchange Partition In Compressed Table With Unused Columns (Doc ID 1532266.1)
Solution
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
Create a dummy table with same structure, including unused columns. Attributes of unused columns can be found in dba_tab_cols (column_name is not important).
For example:
CREATE TABLE <TABLE_NAME> (
prod_id NUMBER(6), cust_id NUMBER, time_id DATE, amount_sold NUMBER(10,2)
) compress tablespace online_tbs
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY'))
);
ALTER TABLE <TABLE_NAME> SET UNUSED (cust_id);
select * from dba_tab_cols where table_name='<TABLE_NAME>' order by INTERNAL_COLUMN_ID;
insert into <TABLE_NAME> values(2, to_date('02.05.1998', 'dd.mm.yyyy'),2);
insert into <TABLE_NAME> values(3, to_date('02.09.1998', 'dd.mm.yyyy'),3);
insert into <TABLE_NAME> values(4, to_date('02.12.1998', 'dd.mm.yyyy'),4);
commit;
CREATE TABLE <TABLE_NAME1> (
prod_id NUMBER(6), test_col NUMBER, time_id DATE, amount_sold NUMBER(10,2)
) compress;
ALTER TABLE <TABLE_NAME1> SET UNUSED (test_col);
alter table <TABLE_NAME> exchange partition SALES_Q3_1998 with table <TABLE_NAME1> EXCLUDING INDEXES WITHOUT VALIDATION;