ORA-14097 - when using materialized view and partition table

This one was an interesting issue which came up few days back. I spent quite sometime before I solved it.

Issue was - A developer came upto me and told that he is getting "ORA-14097 - : column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" while exchanging partitions.

This guy is working on a warehouse design in which huge data loads will happen on base tables through out the day and there are mviews based on which it mines and these mviews' data should be moved fact tables at midnight 12.

Now we strategize in a way that at midnight when base tables will be truncated for fresh load of next day, we exchange the partitions of these mviews with temporary partitioned tables and use "Insert /*+ append */" to move it to final fact tables. We could not have directly exchanged partitions of fact table as they might not be partitioned by day.

Now the above is all background. While investigating the issue, I was perplexed for some time when he showed me the issue. He created a temporary partitioned table using "create table as select .. the mview where 1=2" and while doing exchange partition he was getting ORA-14097.

Let me give you a simple test case:

SQL> create table test(a number);

Table created.

SQL> create materialized view log on test with rowid including new values;

Materialized view log created.

SQL> create materialized view test_mv refresh fast on demand with rowid as select * from test;

Materialized view created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('test_mv','F')

PL/SQL procedure successfully completed.

Now both table and mview have on erow each.

Let's try and exchange partition of this mview with another table.

SQL> select partition_name from user_tab_partitions where table_name='TEST_PART';

PARTITION_NAME
------------------------------
SYS_P3446

SQL> alter table test_part exchange partition SYS_P3446 with table test_mv;
alter table test_part exchange partition SYS_P3446 with table test_mv
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


I used CTAS, there is no question abt data type and column order.

SQL> desc test_part
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> desc test_mv
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER


After doing some research, i got through this metalink article: 72332.1

According to that : "If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail. Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference."

SQL> select col#, name
2 from sys.col$
3 where obj# in
4 (select object_id from user_objects where object_name = 'TEST_MV');

COL# NAME
---------- ------------------------------
0 M_ROW$$
1 A

Now there you go - this M_ROW$$ was creating the problem for me.

Old saying - identifying is problem is 80% of tak that entails solving it.

Now - i created test_mv using primary key (not using rowid) and the whole exchange process worked fine!

For those who donot have PK in their tables can consider having a separate column which can be seeded using a sequence and treat that as PK to combat this issue.

Though it was trivial issue and solution. it kept me thinking for some time!