ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

Few days back, I was working on one of the UAT environments  during that deployment there is requirement to add additional four columns on SWAP & Those two tables are SWAP and FACT tables.  Swap table is very small table and FACT is which is very big table which contains the millions of rows.  As a rollback  We made drop the those columns from SWAP table since it will not take much time to drop its small table and Set unused those four columns on FACT table since its very big table. so as to drop columns from 2 tables which were already set to unused as per request from one of my application team.

After Deployment completed Developer team having issues while running ETL Jobs and which are failing with Below error while doing partition exchange between swp and fact tables.

ORA Error Message : ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

ALTER TABLE SUNIL.XXXXX_FACT EXCHANGE PARTITION SYS_P1299790 WITH TABLE SUNIL.XXXXXX_SWP INCLUDING INDEXES WITHOUT VALIDATION;

Note: For identifying unused columns for any table, you need to query dba_tab_cols view as follows. If count is greater than zero then table has unused columns in it. This columns will not be visible in view dba_tab_columns. When we set columns as unused it will not visible while describe table and oracle internally it will make columns which will start with SYS%XXX$.

select count(0) from dba_tab_cols where table_name= and column_name like ‘SYS%$’;

To VIEW FROM UNUSED COLUMNS BY ANY SPECIFIC SCHEMA:-

 select * from dba_unused_col_tabs where owner='SUNIL';

OWNER                          TABLE_NAME            COUNT

------------------ ------------------------------ ----------

SUNIL                      XXXXXX_SWP                   0

SUNIL                      XXXXXX_FACT                  4

To See Unused columns in Tables its visible from dba_tab_cols or All_tab_cols.

 select column_name,  column_id, internal_column_id

 from   dba_tab_cols

 where  table_name = 'T'

 order by internal_column_id

 /

select column_name  from   dba_tab_cols where  table_name = 'PAR';

As soon as Developer contact me I came to know its issue with UNUSED columns only  between SWP and FACT Tables.  Now I have Two  solutions to fix this  issue.Since these two tables are involved in Partition Exchange . For Partition Exchange Table Structures should be same including number of columns and data types and Indexes and so on .  In this case on fact table having  4 unused columns and swap table doesn’t have it.

Veriy Is there any unused columns between SWP and FACT Tables by using below query

select * from dba_unused_col_tabs where owner='SUNIL';

OWNER                          TABLE_NAME            COUNT

------------------ ------------------------------ ----------

SUNIL                      XXXXXX_SWP                   0

SUNIL                      XXXXXX_FACT                  4

In above we can see FACT table having 4 Unused columns I knew what are those since I made in previous deployment made them unused and dropped columns on SWP Table.

Solution 1 )  Drop the Unused columns from  FACT table  to make tables structures same.

alter table SUNIL.XXXXXX_FACT drop unused columns;

select * from dba_unused_col_tabs where owner='SUNIL';

OWNER                          TABLE_NAME            COUNT

------------------ ------------------------------ ----------

SUNIL                      XXXXXX_SWP                   0

SUNIL                      XXXXXX_FACT                  0

Above solution it will work if your table sizes are small other wise it will take few hours to Drop unused columns.

Solution 2) Since in my case fact table is very big table I know it will take couple of hours to drop unused columns I will add same columns to SWP table and set unused those columns so Struture wise both SWP and FACT table will be same.

alter table SUNIL.XXXXXX_SWP add ( A init, b init, c init, d init );

ALTER TABLE SUNIL.XXXXXX_SWP  SET UNUSED ( A,B,C,D);

Now Compare Unused Columns in Both Tables :-

select * from dba_unused_col_tabs where owner='SUNIL';

OWNER                          TABLE_NAME            COUNT

------------------ ------------------------------ ----------

SUNIL                      XXXXXX_SWP                   4

SUNIL                      XXXXXX_FACT                  4

After that I requested to developer to run ETL JOB it went fine since structure wise its same when you compare Unused columns as well. 

There is few limitations is there

1)    you cant drop unused columns on compression tables . To drop unused columns we need to uncompressed the table and drop unused columns. 

2)   There is  few other  limitations  when you are trying to drop columns on tables which are enabled advanced OLTP compression that statement will executed successfully but its actually not dropping the columns it will make columns as unused in background instead of uncompressed and drop column and compress table  instead of drop the columns.

3) when you are trying to drop columns where  Basic Compression is enabled on table  it will not let you allow you to drop the columns. it will give you below errors. 
   
ERROR at line 1:

ORA-39726: unsupported add/drop column operation on compressed tables

I will explain in my next post how to drop the columns or unused columns on compressed tables.