物化视图:基表增加删除列

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表删除列会导致物化视图刷新失败，但对增列的删除不受影响．

不能重命名物化视图