开始学习物化视图，想知道物化视图怎么建立的，就找了点资料，进行实践，操作如下：

以DBA权限用户登录；

SQL> grant  create materialized view to study;

授权成功。

SQL> grant query rewrite to study;

授权成功。

SQL> grant create any table to study;

授权成功。

SQL> grant select any table to study
  2  ;

授权成功。

以study用户登录；

SQL> desc semp
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select empno,ename from semp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      8000 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER
      8100 geng
      8100 geng

已选择16行。

SQL> delete from semp where empno=8100;

已删除2行。

SQL> commit;

提交完成。

SQL> alter table semp add constraint pk_emp primary key(empno) enable novalidate
;

表已更改。

SQL> commit;

提交完成。

SQL> alter table sdept add constraint pk_dept primary key(deptno) enable novalid
ate;

表已更改。

SQL> commit;

提交完成。

SQL> create materialized view log on sdept;

实体化视图日志已创建。

SQL> create materialized view log on semp;

实体化视图日志已创建。

SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.deptno,e.empno,d.dname,ename,e.job from semp e,sde
pt d where d.deptno=e.deptno;
create materialized view vm_remp build immediate refresh fast on commit enable q
uery rewrite as select d.deptno,e.empno,d.dname,ename,e.job from semp e,sdept d
where d.deptno=e.deptno

                                                                 *
第 1 行出现错误:
ORA-12052: 无法快速刷新实体化视图 STUDY.VM_REMP


SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.dname,ename,e.job from semp e,sdept d where d.dept
no=e.deptno;
create materialized view vm_remp build immediate refresh fast on commit enable q
uery rewrite as select d.dname,ename,e.job from semp e,sdept d where d.deptno=e.
deptno

                                                *
第 1 行出现错误:
ORA-12052: 无法快速刷新实体化视图 STUDY.VM_REMP


SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.rowid did,e.rowid eid, d.deptno,e.empno,d.dname,en
ame,e.job from semp e,sdept d where d.deptno=e.deptno;
create materialized view vm_remp build immediate refresh fast on commit enable q
uery rewrite as select d.rowid did,e.rowid eid, d.deptno,e.empno,d.dname,ename,e
.job from semp e,sdept d where d.deptno=e.deptno


          *
第 1 行出现错误:
ORA-12032: 不能使用 "STUDY"."SDEPT" 上实体化视图日志中的 rowid 列


SQL> create materialized view log on sdept with rowid;
create materialized view log on sdept with rowid
*
第 1 行出现错误:
ORA-12000: 实体化视图日志已经存在于表 'SDEPT' 上

SQL> drop materialized view log on sdept;

实体化视图日志已删除。

SQL> drop materialized view log on semp;

实体化视图日志已删除。

SQL> create materialized view log on sdept with rowid;

实体化视图日志已创建。

SQL> create materialized view log on semp with rowid;

实体化视图日志已创建。

SQL> create materialized view vm_remp build immediate refresh fast on commit ena
ble query rewrite as select d.rowid did,e.rowid eid, d.deptno,e.empno,d.dname,en
ame,e.job from semp e,sdept d where d.deptno=e.deptno;

实体化视图已创建。

SQL>

关于物化视图的详细资料可以看到老杨的博客：http://yangtingkun.itpub.net/post/468/14245