找到你要的答案

Q:Add column to materialized view

Q:添加列到物化视图

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!

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 ?

谢谢!

answer1: 回答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;

将列添加到基表,然后在MV中运行刷新。

declare
begin
  DBMS_SNAPSHOT.REFRESH('your_mv', 'f');
end;
answer2: 回答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';
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';
oracle  materialized-views