We changed the underlying table column size (length). So the materialized view which is built on that table throws an error now since the MV column still has the old size.

This is an Oracle 11gR2 database.

How can I change the MV column's length to match the table's new column size without dropping the MV and recreating it?

  • Did you tried to compile the materialized view? alter materialized view <your_mv_here> compile Commented Dec 19, 2012 at 20:31
  • i tried, the column size/length is still the same, not changed to the table's column new size/length...
    – magqq
    Commented Dec 19, 2012 at 21:09

2 Answers 2


You have to drop and recreate.

If you look at the documentation here, you can see that only modification of the storage clauses are allowed.


It is not shown in the Oracle documentation, but you can simply use alter clause to change the column length in mview:

alter materialized view xxx modify (col varchar2(new_size));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.