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! 🙂

Print Friendly
December 13th, 2009 by

facebook comments:

  • Chan

    I did try with range-hash partition. I uncompress hash subpartition first and try to drop unused column and got “ORA-39726: unsupported add/drop column operation on compressed tables ” . I tried to uncompress range partition and I received “ORA-14257: cannot move partition other than a Range, List, System, or Hash partition”. Appreciate your advice.

  • I don’t have any experience with this situation with hash partitions. I would consider that it would work, just as it did for any other partition style, but the CTAS must include ALL the columns in the exact same column# order as the original and then made invisible, just as the original had.

    Good luck,
    Kellyn

  • ayub

    Hi Kellyn, how about using online redefinition to drop unused columns so that this whole exercise of uncompress, drop and compress to be avoided and to always have the table available.

    Also how much time did it take for the whole exercise for this 250gb table?

  • Pingback: EM12c and the Optimizer Statistics Console - Oracle - Oracle - Toad World()

  • andyk1985

    I ran into a similar problem with a very large table, and I could not take the downtime to drop the unused coiumn (full table lock=downtime for us). In my case, I had used dbms_redefinition to partition the large table, so I knew the source of the unused column. So I used redefinition to make the destination table of the exchange partition operation the same as the original by simply not dropping the unused column created during the dbms_redefinition operation. I just created an empty dummy table for the operation so it went very quickly. The exchange partition operation worked perfectly after that. Obviously it wont work for other sources of unused columns, but when they come from a dbms_redefinition operation, this method solves the problem.

  • Facebook
  • Google+
  • LinkedIn
  • Twitter