通过案例学调优之–跨库建立物化视图（MaterializedView）应用环境：操作系统：RedHatEL55Oracle：Oracle10gR2一、物化视图概述Oracle的物化视图是包括一个查询
在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。
案例分析：
本案例架构
1）在test1库上建立db link
tnsnames.ora:
[oracle@rh6 admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.TEST1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.cuug.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test1) ) )PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rh6.cuug.net)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) ) )在prod库上建立tom用户，并授权15:18:08 SYS@ prod >create user tom identified by tom;User created.15:18:27 SYS@ prod >grant connect,resource to tom;Grant succeeded.15:18:49 SYS@ prod >grant all on scott.emp1 to tom;Grant succeeded.在test1库上建立db link15:12:12 SYS@ test1 >grant create database link,create public database link to tom;Grant succeeded.15:13:59 TOM@ test1 >create database link db_link_prod connect to tom identified by tom using ‘prod’;Database link created.测试：15:19:10 TOM@ test1 >select * from scott.emp1@db_link_prod; EMPNO ENAME JOB MGR HIREDATE 来源gaodai#ma#com搞@代~码$网 SAL COMM DEPTNO———- ———- ——— ———- ——————- ——– 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1014 rows selected.
转载请注明原文链接：通过案例学调优之–跨库建立物化视图（Materialized View）
