Materilaized view - remove column

[sln81]

Can a column be dropped from a materialized view?

[Sharma]

Yes, U Can.

SQL> create materialized view testv as select sysdate onecol, sysdate twocol from dual;

Materialized view created.

SQL> desc testv
Name Null? Type
----------------------------------------- -------- ----------------------------
ONECOL DATE
TWOCOL DATE

SQL> alter table testv drop column twocol;

Table altered.

SQL> desc testv
Name Null? Type
----------------------------------------- -------- ----------------------------
ONECOL DATE

[fraze]

That only drops the column off the under lying table - if you check DBA_OBJECTS for the view it will be invalid, and the recompile will fail. I'm pretty sure you need to drop and re-create it