Add column to materialized view

Question

I have an existing materialized view in Oracle. But I want to add more columns to it. I saw that it is not possible to execute CREATE OR REPLACE to the materialized view so how can I do that ?

Thanks!

Answer 1

Add the columns to the base table and after that run a refresh in the MV.

declare
begin
  DBMS_SNAPSHOT.REFRESH('your_mv', 'f');
end;

Answer 2

select * from dba_objects where status='INVALID';

grant create materialized view to <schema>;
grant create table to <schema>;

drop MATERIALIZED VIEW <schema>.<mvname>;

  CREATE MATERIALIZED VIEW <schema>.<mvname>
  (...)
  AS SELECT (...);

select * from  <schema>.<mvname>;


revoke create materialized view from <schema>;
revoke create table from <schema>;

select * from dba_objects where status='INVALID';