HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Exchange partition issues ORA-14096 and ORA-14097
ORACLE STUFFS
03-Aug-2008
On the way of exchange partition there are two basic prerequisities you have to satisfy:
  • Equal number and data types of columns.
  • Each local index of partitioned table should have an equivalent as an index of exchanged table (valid for constraints).
Despite it sounds simply, it is often not as easy getting rid of troubles such as:
  • ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns.
  • ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.

The ORA-14096 error follows usually 3 causes:

  • Different table structure (column list).
  • Unusable columns.
  • Snapshot (Materialized view) logs based on rowid.
The ORA-14097 error follows usually 3 causes:
  • Different table structure (data types).
  • Different constraints using local indexes.
  • Different check constraints.

The simpliest cause - Different table structure - we can identify using a simple script:

Select a.COLUMN_NAME
  , a.DATA_TYPE, b.DATA_TYPE
  , a.data_length, b.data_length
  , a.data_precision, b.data_precision
  , a.data_scale, b.data_scale
  , a.nullable, b.nullable
from ALL_TAB_COLUMNS a
full outer join ALL_TAB_COLUMNS b on a.column_name=b.column_name
 and b.owner=user and b.table_name='&table2'
 where  a.owner=user and a.table_name='&table1'  
 and (
    nvl(a.data_type,'#')!=nvl(b.data_type,'#')
    or nvl(a.data_length,-1)!=nvl(b.data_length,-1)
    or nvl(a.data_precision,-100)!=nvl(b.data_precision,-100)
    or nvl(a.data_scale,-100)!=nvl(b.data_scale,-100)
    or nvl(a.nullable,'#')!=nvl(b.nullable,'#')
   )
;

[Download]

The second cause - different indexes (I have tested it a and the problem is from the Oracle 10.2 version just cause of primary key and unique constraints). To synchronize indices you can use following script:

Script: Consolidate_indexes.sql

Declare
  p_target_owner Varchar2(30):='&partitioned_table_owner';
  p_target_table Varchar2(30):='&partitioned_table_name';
  p_source_owner Varchar2(30):='&simple_table_owner';
  p_source_table Varchar2(30):='&simple_table_name';
  TYPE       ta_ht2000 is table of Varchar2(2000) index by Varchar2(2000);
  va_indexes ta_ht2000;
  v_columns  Varchar2(2000);
  v_delim    Varchar2(1);
  v_hash     Varchar2(2000);

...more

Anyway, you can meet the case even if everything (columns, constraints and indexes) are equal and Oracle tells you about different structure, it is probably cause of unused columns. If you have enough rights to access system tables, you can check it using following query:

select COLUMN_NAME as NAME from ALL_TAB_COLS where table_name='&table_name';

The script shows you full list of columns including unused and system. Result like:

NAME ------------------------------ C1 C2 C3 C4 SYS_C00005_08080322:13:07$

signalizes invisible columns. If it is unused one, you can check the state using following query:

select * from ALL_UNUSED_COL_TABS 
where table_name ='&table_name';

The query returns just number of unused columns if they are. The easist way is to drop unused columns in table, but it takes time if the table is large and it is impossible if some of partitions if compressed.


How to avoid problems
The most common reason of mentioned problems is default data type when create the source table for exchange. You can create table using following:

Create table ... as select * from ... where 1=0

That is safe.
Often you have to create the table as select using real data with other structure. It is hard to modify datatype from less coustrained (e.g. NUMBER) to more constrained (e.g. NUMBER(10)). You have to justify structures using the cast function when select them from source structure.

Create table ... as select ... cast(<column> as <required datatype>) ...

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky