oracle物化视图能否删除列,物化视图:基表增加删除列

SQL> create table lgx (a int ,b varchar2(10));

Table created

SQL> insert into lgx values( 1, 'a');

1 row inserted

SQL> commit;

Commit complete

SQL> CREATE MATERIALIZED VIEW LOG ON lgx WITH ROWID;

Materialized view log created

SQL>

SQL> CREATE MATERIALIZED VIEW mv_lgx

2  REFRESH FAST ON DEMAND

3  WITH ROWID

4  START WITH TO_DATE('17-08-2009 17:09:16', 'DD-MM-YYYY HH24:MI:SS') NEXT /*1:HRS*/ SYSDATE + 1/(24*60)

5  AS

6  SELECT * FROM lgx;

Materialized view created

SQL>

SQL> select * from mv_lgx;

A B

--------------------------------------- ----------

1 a

SQL> select job,what from all_jobs;

JOB WHAT

---------- --------------------------------------------------------------------------------

145 dbms_refresh.refresh('"LGX_TEST"."MV_LGX"');

SQL> alter table lgx add (c int);

Table altered

SQL> insert into lgx values( 2, 'a',1);

1 row inserted

SQL> commit

2  ;

Commit complete

SQL> select * from lgx;

A B                                                C

--------------------------------------- ---------- ---------------------------------------

1 a

2 a                                                1

SQL> select * from mv_lgx;

A B

--------------------------------------- ----------

1 a

SQL> exec dbms_job.run(145);

PL/SQL procedure successfully completed

SQL> select * from mv_lgx;

A B

--------------------------------------- ----------

1 a

2 a

SQL> alter table lgx drop (c );

Table altered

SQL> exec dbms_job.run(145);

PL/SQL procedure successfully completed

SQL> select * from mv_lgx;

A B

--------------------------------------- ----------

1 a

2 a

SQL> alter table lgx drop (b ) ;

Table altered

SQL> select * from lgx;

A

---------------------------------------

1

2

SQL> exec dbms_job.run(145);

PL/SQL procedure successfully completed

SQL> select * from mv_lgx;

A B

--------------------------------------- ----------

1 a

2 a

SQL> insert into lgx values(3);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from lgx;

A

---------------------------------------

1

2

3

SQL> exec dbms_job.run(145);

begin dbms_job.run(145); end;

ORA-12011: execution of 1 jobs failed

ORA-06512: at "SYS.DBMS_IJOB", line 406

ORA-06512: at "SYS.DBMS_JOB", line 272

ORA-06512: at line 2

SQL> rename mv_lgx to mv_lgx2;

rename mv_lgx to mv_lgx2

ORA-32318: cannot rename a materialized view

SQL>

结论:

master表增加列对物化视图的刷新没有影响,但增加的新列不能在物化视图中显示,需要重建物化视图．

master表删除列会导致物化视图刷新失败，但对增列的删除不受影响．

不能重命名物化视图