„Error in materialized view refresh path”

Hi there,

here’s just a small tip for those of you who are using Oracle’s materialized views with the refresh-on-commit option. Consider a following scenario:

  1. Create a table (let’s call it „X”) with a not-null column
  2. Create a materialized view („Y”) that has to refresh itself each time a record into the table „X” is inserted (or modified, deleted.. REFRESH FORCE ON COMMIT)
  3. Change the column created in point 1 to allow null values.
  4. Insert data into table „X”, with a null value of the column mentioned in point 1.

Most likely you’ll get an Oracle error, stating the following:

ORA-12008: error in materialized view refresh path

Further exception may also look similar to this:

ORA-01400: cannot insert NULL into …

The second one speaks for itself. Since the materialized view is actually a snapshot, it has „remembered” the not-null setting of the column of the table „X”. And when inserting a new record, it complains about the not-null constraint.

Drop the materialized view, re-create it, problem solved. The new one has the new column definition, allowing nulls in the column.

Hope this helps,