(转)Oracle DBLINK 简单使用以及建立物化视图

原文地址：https://www.cnblogs.com/wangyong/p/6354528.html

oracle在进行跨库访问时，可以通过创建dblink实现，今天就简单的介绍下如果创建dblink，以及通过dblink完成插入、修改、删除等操作

首先了解下环境：在tnsnames.ora中配置两个数据库别名：orcl(用户名：wangyong 密码：1988)、orcl2(用户名：wangyong 密码：123456)，在orcl中     创建database link来访问orcl2

第一步：赋予权限

     在创建database link之前，我们需要判断，登陆的用户是否具备创建database link 的权限，所以我们执行以下的语句(用wangyong用户登陆orcl)：

– 查看wangyong用户是否具备创建database link 权限

select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='WANGYONG';
        如果查询有返回行，则表示具备创建database link权限，否则，则需要使用sys登陆orcl为WANGYONG用户赋予创建权限

– 给wangyong用户授予创建dblink的权限

grant create public database link to wangyong;
         此时，再执行上面查看是否具备权限的sql语句，会发现有返回行，表示，WANGYONG这个用户已经具备创建database link的权限

第二步；创建database link

     我所了解到的创建方式有两种：1)通过pl/sql developer图形化创建、2)通过sqlplus中的sql语句创建，依次来看
     1)pl/sql developer 图形化创建
       填写完成后点击“Apply”按钮即可创建成功。
	 2)sql语句创建

– 注意一点，如果密码是数字开头，用“”括起来

create public database link TESTLINK2 connect to WANGYONG identified by "123456" USING 'ORCL21'
       这样，就完成了简单database简单的创建

第三步：操作

      首先，我们需要在ORCL2库中新建一张表，并插入部分数据，如下图：
      现在，我们通过database link 在orcl中访问这张属于orcl2库中WANGYONG的表COMPANY从截图中可以看到，在ORCL中可以成功访问到ORCL2中用户WANGYONG的表下面，利用同样的方式，进行插入，修改，删除操作，依次看截图，每一次操作后均执行查询语句，可对比执行效果：

下面，利用同样的方式，进行插入，修改，删除操作，依次看截图，每一次操作后均执行查询语句，可对比执行效果：

1)插入

2)修改

3)删除

 至此，简单的dblink操作就可以了，对于上面的链接字符串，还可以创建同义词代替，会稍微省点事

– 创建同义词

create synonym TESTSYNONYM FOR company@TESTLINK1;
     那么上面的查询、插入、修改、删除中可直接用WYSYNONYM代替company@TESTLINK1即可，例如查询语句可改成如下方式（插入，修改，删除类似）：

-- 查询ORCL2中WANGYONG用户的表COMPANY
SELECT * FROM TESTSYNONYM order by id

新建物化视图

物化视图主要应用于一些比较固定的查询业务，比如报表中的查询历史数据等，由于物化视图可以将大量join连表查询的数据存储在一张表中，可以加快数据查询速度，减少由于join导致的查询速度变慢问题。

--从远程数据库同步新建物化视图
--1.场景：需要同步过来的表名叫：GG_ZLX_ZHU，对方数据库用户名：username，密码：password，SID：CPEES
--2.语句：
--      1)首先要创建DB_LINK
        CREATE DATABASE LINK to_cpees CONNECT TO "username" identified by "password" using "TO_CPEES";
--      2)创建Oracle物化视图快速刷新日志,视图的刷新将采用增量刷新的方式，因此，为配合增量刷新，ORACLE要求要在住表上建立物化视图日志
--        因为增量刷新就是通过找到相应的rowid和rowid匹配的数据，来进行刷新的。
        --该SQL要在远程数据库上执行，不能在本地执行
        CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU WITH PRIMARY KEY INCLUDING NEW VALUES;
        --(简化操作为：CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU WITH ROWID;根据ROWID查询数据并刷新)
--3.创建Oracle物化视图
        --创建物化视图
        CREATE MATERIALIZED VIEW GG_ZLX_ZHU
        --在视图编写好后创建
        BUILD IMMEDIATE
        --根据主表主键增量刷新（FAST，增量）  -- 在用户需要时，由用户刷新
        REFRESH FAST WITH PRIMARY KEY ON DEMAND ENABLE QUERY REWRITE AS SELECT * FROM GG_ZLX_ZHU@TO_CPEES;
--4、视图刷新
        CREATE OR REPLACE PROCEDURE P_MVIEW_REFRESH AS
        BEGIN
          DBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f');--f、增量刷新，c、完全刷新，?、强制刷新
        END P_MVIEW_REFRESH;
--5.删除方法:日志和物化视图要分开删除
        DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;
        DROP MATERIALIZED VIEW GG_ZLX_ZHU;
--6.注意：
-- 1)如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新）。
-- 2)当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量刷新。
-- 3)因为上面写的物化视图时根据主键进行更新，因此，主表必须有主键。

--7.简单创建语句(REFRESH FORCE ON COMMIT:只要源数据库表的数据有变动就会强制同步刷新,这样刷新是全表的刷新)：
    CREATE MATERIALIZED VIEW GG_ZLX_ZHU REFRESH FORCE ON COMMIT AS SELECT * FROM EMP;
--8.定时手动刷新
    CREATE MATERIALIZED VIEW GG_ZLX_ZHU REFRESH FORCE ON DEMAND
      START WITH sysdate--当前时间
      NEXT sysdate+1/1440--每分钟刷新一次
      AS SELECT * FROM EMP;

视图如何传参
物化视图可以减少join连接的使用，提高数据查询的效率，那么问题来了，在java中间层如何进行传参数操作呢？特别是在报表操作中，要如何在中间层将参数传递给oracle呢？请参照如下文章：

https://blog.csdn.net/hy245120020/article/details/50009737