oracle 创建物化视图

mview   --------------------materialized view

 

实现用户king创建scott用户下的emp表

首先授予用户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>

 

创建for update物化视图

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;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29532781/viewspace-1174698/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29532781/viewspace-1174698/