Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Dec 19 '12 at 20:03

This question came from our site for professional and enthusiast programmers.

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

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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