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

A developer needed to do some serious data changes to a partition. I thought I’d be a helpful DBA and suggested he do a CTAS of the one partition, make the change and we’d exchange the partition.

He was thrilled when the update statement that I’d killed after 24 hrs of no successful runs was cut down to 70 seconds with the CTAS and then all we needed to do was have me exchange it back in-

ALTER TABLE TAB1 EXCHANGE PARTITION P4706 WITH TABLE TAB_CPY_4706;

ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

I did a quick compare of the table columns, datatypes and if the columns allowed nulls- no differences- so what’s up?

select col#, name
from sys.col$
where obj# in
(select object_id from dba_objects where owner=’TAB_OWNER’ and object_name = ‘TAB1’)
order by col#;

COL# NAME
0 SYS_C00029_09042414:39:06$
0 SYS_C00017_09042414:38:51$
0 SYS_C00028_09042414:39:00$
1 Column1
2 Column2 …

The columns #’s with 0 are “Unused Columns”, a feature that allows you to move, (or set) columns to unused and then drop them at a maintenance window if none is available at the time the column needs to be removed from a table. The column is then invisible to standard processing, but will cause issues in partitioned tables if you attempt to exchange a partition.

There is a way to create your CTAS, then add the unused columns to the table with all the appropriate names, datatypes, etc. to the table you wish to exchange with, then attempt to exchange that, (first setting the columns to unused in the table before exchanging, but so that it matches the existing partition format) but I haven’t attempted it, only heard of the theory.

The long-term fix for this, as exchanging partitions would be a nice load option without fancy workarounds, is to drop the unused columns. This is my goal and what I decided needed to be achieved this weekend so we didn’t go through this again:

ALTER TABLE TAB1 DROP UNUSED COLUMNS;

ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Here’s the second challenge for the day- this large, partitioned table, happens to be compressed and you can’t drop unused columns from a compressed table. This feature is only available on tables that have compression disabled on them.

The long term fix then demanded that I uncompress all the partitions, (one partition at a time, updating the indexes to allow miscellaneous processing to continue over the weekend…) and then set the table to nocompress option. Note- This is a table move command, so you must ensure you have enough room for table movement and to uncompress the object and indexes involved in the tablespaces!

ALTER TABLE TAB1 MOVE PARTITION P1000 NOCOMPRESS UPDATE INDEXES;
…

ALTER TABLE TAB1 NOCOMPRESS;

Once this was completed, then I could drop my unused columns successfully:

ALTER TABLE TAB1 DROP UNUSED COLUMNS;

This is a long operation, as the process must drop the columns from every partition, which can be a considerable effort in a 250GB table, as this one was. After finally dropping those pesky columns, I re-added compression to the table and compressed the appropriate partitions.

No one said DBA work was always interesting, but today it was a battle I could win! 🙂