This error sooner or later comes when you have at least mid-sized data warehouse.
And is one of the funniest. Because on the first look, everything looks all right.
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Let’s begin with some basics – Exchanging partitions occurs when you switching partition with another one (or with a table).
One of your tables is partitioned (obviously) – usually the big one with the history in your data warehouse (usually partitioned by date/time).
When you adding new iteration (i.e. day) – first you prepare your daily snapshot to the separated table – with the exact same structure.
as your target history table. Then prepare new partition in your history table (ie. partition_20150102 )
and then instead of copying data to the history table, you can easily switch these partitions:
ALTER TABLE TABLE_HISTORY exchange partition partition_20150102 with table TABLE_TODAY
Everything goes nicely until one day… ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
This usually happens when you modify your table in some way. Be very careful with Indexes, columns types, columns drops and default values.
For example, when you have dropped one column – from TODAY and HISTORY table. Everything should look fine, but sometimes Oracle decides that he will not drop this column physically, but just mark them ‘UNUSED’ – especially at big and/or compressed tables.
You can quickly check for this:
select * from ALL_UNUSED_COL_TABS t where t.TABLE_NAME = 'TABLE_HISTORY';
or with:
select col#, name
from sys.col$
where obj# in
(select object_id from dba_objects where owner='TABLE_OWNER' and object_name = 'TABLE_HISTORY')
order by col#;
where there is an indication of any unused columns you can use this:ALTER TABLE TABLE_OWNER.TABLE_HISTORY DROP UNUSED COLUMNS;
This can fix it, but not always – when your table is compressed you can get the following error:
ORA-39726: unsupported add/drop column operation on compressed tables.
At this point, you stand against bigger problem ;-), but it has a solution – everything has.
You can either decompress your table or recreate it with correct columns and fill it with insert-select. I will show you the first option – which I think is cleaner => better 🙂
First, you need to decompress all your partitions one by one – you can use the following script with slight modifications:
DECLARE
CURSOR p_cur IS
SELECT p.PARTITION_NAME
FROM USER_TAB_PARTITIONS p
where p.TABLE_Name = 'TABLE_HISTORY ';
partition_name varchar2(200);
BEGIN
OPEN p_cur;
LOOP
FETCH p_cur INTO partition_name;
EXIT WHEN p_cur%NOTFOUND;
DBMS_OUTPUT.put_line('NOCOMPRESS partition: ' || partition_name);
execute immediate( 'ALTER TABLE TABLE_HISTORY MOVE PARTITION '|| partition_name ||' NOCOMPRESS UPDATE INDEXES');
END LOOP;
CLOSE p_cur;
END;
Then you can easily set your table NOCOMPRESS and drop those UNUSED columns:
ALTER TABLE TABLE_OWNER.TABLE_HISTORY NOCOMPRESS;
ALTER TABLE TABLE_OWNER.TABLE_HISTORY DROP UNUSED COLUMNS;
Don’t forget to Compress again 😉
If you don’t have any unused column – take a detailed look at your default values – you will probably need to recreate your table and move your data – I strongly suggest to move data partition by partition.
