oracle 建立物化檢視

首先授予使用者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;