RDBMS -- Failed add column operation may cause ORA-14097 & ORA-14278 during Partition Exchange

Recently an interesting issue escalated to me..

Customer was complaining about failed queries on a partitioned table..

The problem was obvious.. This database environment was created using rman duplicate with "SKIP TABLESPACE".. Well, SKIP TABLESPACE options was used for the duplicate, and the problem was caused by the missing datafiles mapped to some partitions of that partitioned table..

Note that, database version was 18C and customer didn't need the data in those problematic partitions.

Before, escalating the issue, customer also tried to add some columns to this table and got ORA-00376, ORA-01111 and ORA-01110.. Actually, this was no suprise.. The datafiles of some partitions were missing, as a result of duplicating this database with SKIP tablespace option.

In this kind of a case, we create a new empty dummy table with the same structure as our original partitioned one, and then we exchange partitions between this new empty table and our partitioned table to get rid of those empty/unneeded partitions which are mapped to missing data files..

However; customer tried to add a column to this table without this partition exchange thing and got those errors mentioned above.

Also, this failed add column action left unused column in the partition table... So because of those unused columns in the table, exchange partition action was encountering "ORA-14278: column type or size mismatch in EXCHANGE SUBPARTITION".

Here is the action plan to workaround this situation;

• alter session set events '14529 trace name context forever, level 512'
• CTAS  to create the new empty partitioned table
• EXCHANGE PARTITION for those problematic partitions between the newly created partitioned table and the original one..

Important  Note: "Level 512" is a must. I see similar blog posts and Oracle Support notes, but level 512 is missing. "In this case", using Level 512 in that event is a must.

Note that; -> event 14529 has been enhanced with level 512 to handle the cases like the following  for exchange partition operations;

unusable columns, invisible columns

fast add of nullable columns with default value

fast add of not nullable columns with default value, long varchar columns, virtual expression columns and so on..