“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

~ by Łukasz on 4 January 2011.

One Response 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: