首先授予用户king权限

SQL> grant connect,resource to king;   ---连接与资源权限

Grant succeeded.

SQL> grant select on scott.emp to king;   -----查询scott用户下的emp表权限

Grant succeeded.

SQL> grant create materialized view to king;   ---创建物化视图权限

Grant succeeded.

SQL> grant execute on dbms_mview to king;   ---可以使用dbms_mview包的权限

Grant succeeded.

SQL>

同样要授予scott用户创建物化视图权限

SQL> grant create materialized view to scott;  

Grant succeeded.

SQL>

登录king用户确认能访问scott下的emp表

SQL> conn king/oracle

Connected.

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

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

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80           800

        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300

        30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500

        30

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM

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

  …….

创建物化视图

SQL> create materialized view emp as  select * from scott.emp;

Materialized view created.

SQL>

更新scott用户下的emp表在查询king用户下的物化视图发现不同步

SQL> conn  scott/tiger

Connected.

SQL> update emp set sal=sal+1 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

SQL> select sal from emp where empno=7369;

       SAL

----------

       801

SQL>

SQL> conn  king/oracle

Connected.

SQL> select sal from emp where empno=7369;

       SAL

----------

       800

SQL>

可以刷新物化视图

SQL> exec dbms_mview.refresh('emp','complete');   ---complete为全部刷新数据量太大很不现实

PL/SQL procedure successfully completed.

SQL>

SQL> select sal from emp where empno=7369;

       SAL

----------

       801

SQL>

如果使用快速刷新时无效的----因为没有日志

SQL> exec dbms_mview.refresh('emp','fast');

BEGIN dbms_mview.refresh('emp','fast'); END;

*

ERROR at line 1:

ORA-12004: REFRESH FAST cannot be used for materialized view "KING"."EMP"

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430

ORA-06512: at line 1

SQL>

删除物化视图

SQL> drop materialized view emp;

Materialized view dropped.

SQL>

建立物化视图日志

SQL> create materialized view log on emp with primary key;

Materialized view log created.

SQL>

登录scott用户授予king权限

SQL> show user     

USER is "SCOTT"

SQL> grant on commit refresh on scott.emp to king;

Grant succeeded.

SQL>

King用户下创建物化视图

SQL> conn king/oracle

Connecte

SQL> create materialized view emp refresh fast on commit as select * from scott.emp;

验证

SQL> conn scott/tiger

Connected.

SQL> select sal from emp where deptno=10;

       SAL

----------

      2450

      5500

      1300

SQL> update emp set sal=sal+1;

16 rows updated.

SQL> commit;

Commit complete.

SQL>

SQL> select sal from emp where deptno=10;

       SAL

----------

      2451

      5501

      1301

SQL> conn king/oracle

Connected.

SQL> select sal from emp where deptno=10;

       SAL

----------

      2451

      5501

      1301

SQL> drop materialized view emp;

Materialized view dropped.

SQL>

第一次刷新为当前系统时间，下次刷新为当前系统时间加1分钟

SQL> create materialized view emp refresh fast start with sysdate next sysdate+1/1440 as select * from scott.emp;

Materialized view created.

SQL> select sal from emp where deptno=10;

       SAL

----------

      2451

      5501

      1301

更新源表中数据

SQL> conn scott/tiger

Connected

SQL> update emp set sal=sal-1 ;

16 rows updated.

SQL> commit;

Commit complete.

SQL> select sal from emp where deptno=10;

       SAL

----------

      2450

      5500

      1300

SQL>

查询物化视图

SQL> conn king/oracle

Connected

SQL> select sal from emp where deptno=10;

       SAL

----------

      2451

      5501      ----发现没有同步

      1301

SQL>

等待一分钟再次查询

SQL> select sal from emp where deptno=10;

       SAL

----------

      2450

      5500     -已同步

      1300

SQL>

SQL> create materialized view emp refresh fast for  update as select * from scott.emp;

SQL> update emp set sal=sal+100;    -----可以对物化视图更新一般应用在测试

16 rows updated.

SQL>

测试完成之后可以在同步回去

SQL> exec dbms_mview.refresh('emp','f');

For update也可以设置时间刷新

SQL> create materialized view emp refresh fast start with sysdate next sysdate+1 for update as select * from scott.emp;