„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,
Łukasz

One Reply to “„Error in materialized view refresh path””

  1. Oracle creates a table behind the materialized view. Maybe it is possible to simply drop the null constraint also there (maybe). Yours is definitely the clean way.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *