阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle数据库通过DBLINK实现远程访问

228次阅读
没有评论

共计 4728 个字符,预计需要花费 12 分钟才能阅读完成。

什么是 DBLINK?

dblink(Database Link) 数据库链接顾名思义就是数据库的链接,就像电话线一样,是一个通道,当我们要跨本地数据库,访问另外一个数据库表中的数据时,本地数据库中就必须要创建远程数据库的 dblink, 通过 dblink 本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。

如何使用 DBLINK?

场景:假设当前数据库用户为 ALANLEE,此时需要通过 ALANLEE 这个用户去采集远程数据库的数据。

远程数据库信息如下:

HSAJ216 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 12.1.3.216)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = hscsserver)
    )
  )

远程数据库用户名:hs_user,密码:hundsun

第一步:查看用户是否具备创建 database link 权限

– 查看 ALANLEE 用户是否具备创建 database link 权限
select * from user_sys_privs where privilege like upper(‘%DATABASE LINK%’) AND USERNAME=’ALANLEE’;
select * from user_sys_privs t where t.privilege like upper(‘%link%’);

在数据库中 dblink 有这么一些权限。例如 CREATE DATABASE LINK 表示所创建的 dblink 只能是创建者能使用,别的用户使用不了,CREATE PUBLIC DATABASE LINK 表示所创建的 dblink 所有用户都可以使用,DROP PUBLIC DATABASE LINK 表示删除公用 dblink 的权限。

假如查出相关的数据则表示 ALANLEE 用户具有相关的权限,如果没有查出数据则说明 ALANLEE 用户没有相关的权限。

第二步:假如用户不具备相应的权限则需要授权,如果 ALANLEE 用户具有类似管理员用户授权的权限则直接使用当前用户授权,如果 ALANLEE 不具备这样的权限则使用 SYS/SYSTEM 之类权限更大的数据库用户来给 ALANLEE 用户授权

– 需要授予 ALANLEE 用户创建数据库链接权限
grant create public database link to ALANLEE;
– 需要授予 ALANLEE 用户删除数据库链接权限
grant drop public database link to ALANLEE;

这里我们使用公共的 dblink,即所有用户都可以使用的 dblink,可以根据自己的所需去赋予相应的权限,授权成功后可以通过第一步的视图查看是否授权成功。

第三步:通过 ALANLEE 用户创建远程数据库链接(数据库地址:12.1.3.216 用户名:hs_user 密码:hundsun)

drop public database link HSAJ216;
create public database link HSAJ216
connect to hs_user identified by hundsun
using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 12.1.3.216)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = hscsserver)))’;

为了避免一些其他的问题,这里建议直接使用远程数据库的全局监听实例名作为 database link 的名称,也就是远程数据库信息所示的 HSAJ216。

第四步:查询已经建立的数据库远程链接

select owner,object_name from dba_objects where object_type=’DATABASE LINK’;

如果有自己创建的 database link 数据则说明创建成功,反之就是不存在。

Oracle 数据库通过 DBLINK 实现远程访问

第五步:测试建立的远程数据库链接

select * from dual@HSAJ216;

如果能查出东西,则远程访问便成功了。

Oracle 数据库通过 DBLINK 实现远程访问

如何查询远程数据库某个用户某个表的数据呢?sql 如下:

select * from hs_asset.client@HSAJ216;

如果能查询出表的数据,那就可以开始去做数据采集的工作了,查询出相应的数据,插入本地数据库的表中。

Oracle 数据库通过 DBLINK 实现远程访问

最后一步:通过存储过程采集远程数据库的数据并插入到本地的数据库当中

/**
* 从柜台同步客户数据至临时表
*/
create or replace PROCEDURE SP_SYNC_CUSTOMER_TEMP (UPDATE_TOTAL OUT NUMBER) IS
  INDEX_COUNT NUMBER;
  INDEX_TOTAL NUMBER;
  CURSOR CR IS
    select a.client_id, – 客户编号
          a.branch_no, – 分支机构
          a.id_no, – 证件号码
          a.client_name, – 客户姓名
          a.client_status, – 客户状态
          a.open_date, – 开户日期
          c.fund_account, – 资金账号
          c.main_flag, – 主账标识
          c.asset_prop, – 资产属性
          b.birthday, – 生日日期
          b.address, – 地址
          b.home_tel, – 家庭电话
          b.e_mail, – 邮箱
          b.fax, – 传真
          b.mobile_tel, – 手机号码
          b.office_tel, – 单位电话
          b.zipcode, – 邮政编码
          b.account_data – 开户规范信息
      from hs_asset.client@HSAJ216 a
    inner join (select client_id,
                        birthday,
                        address,
                        home_tel,
                        e_mail,
                        fax,
                        mobile_tel,
                        office_tel,
                        zipcode,
                        account_data
                  from hs_asset.clientinfo@HSAJ216
                union all
                select client_id,
                        ‘19000101’ as birthday,
                        address,
                        contact_tel as home_tel,
                        e_mail,
                        fax,
                        mobile_tel,
                        contact_tel as office_tel,
                        zipcode,
                        ‘A’ as account_data
                  from hs_asset.organinfo@HSAJ216) b
        on a.client_id = b.client_id
    inner join hs_asset.fundaccount@HSAJ216 c
        on a.client_id = c.client_id
    where c.asset_prop = ‘0’;
BEGIN
  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, ‘START- 现在开始执行【SP_SYNC_CUSTOMER_TEMP】清空客户同步临时表 TEMP_SYNC_CUSTOMER…’);
  EXECUTE IMMEDIATE ‘TRUNCATE TABLE TEMP_SYNC_CUSTOMER’;
  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, ‘OVER-【SP_SYNC_CUSTOMER_TEMP】清空客户同步临时表 TEMP_SYNC_CUSTOMER 已完成 …’);

  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, ‘START- 现在开始执行【SP_SYNC_CUSTOMER_TEMP】向客户同步临时表导入数据 TEMP_SYNC_CUSTOMER…’);
  commit;

  INDEX_COUNT := 1;
  INDEX_TOTAL := 0;
  FOR C IN CR LOOP
    – 客户临时表
    INSERT INTO TEMP_SYNC_CUSTOMER
      (CODE,
      ORGA_ID,
      ID_CARD,
      NAME,
      CLOSE_STATUS,
      ACCOUNT_CREATE_DATE,
      CAPITAL_ACCOUNT,
      BIRTHDAY,
      ADDRESS,
      TEL,
      BINDING_EMAIL,
      BINDING_MOBILE,
      MAIN_FLAG
      )
    VALUES
      (C.client_id,
      C.branch_no,
      C.id_no,
      C.client_name,
      C.client_status,
      C.open_date,
      C.fund_account,
      C.birthday,
      C.address,
      C.home_tel,
      C.e_mail,
      C.mobile_tel,
      C.main_flag);
     
    INDEX_COUNT := (INDEX_COUNT + 1);
    INDEX_TOTAL := (INDEX_TOTAL + 1);
   
    IF INDEX_COUNT > 100000 THEN
      COMMIT;
      insert into t_coll_result (id, CREATE_DATE, REMARK)
      values (seq_t_coll_result_id.nextval, sysdate, ‘【SP_SYNC_CUSTOMER_TEMP】已向 TEMP_SYNC_CUSTOMER 导入 ’ || INDEX_TOTAL || ‘ 条数据 …’);
      commit;
      INDEX_COUNT := 1;
    END IF;
  END LOOP;

  insert into t_coll_result (id, CREATE_DATE, REMARK)
  values (seq_t_coll_result_id.nextval, sysdate, ‘OVER-【SP_SYNC_CUSTOMER_TEMP】同步客户临时表 TEMP_SYNC_CUSTOMER 已完成,共导入 ’ || INDEX_TOTAL || ‘ 条数据 …’);
  UPDATE_TOTAL := INDEX_TOTAL;
  COMMIT;
END SP_SYNC_CUSTOMER_TEMP;

当然,我们不可能每次都手动去执行 sql,所以可以结合 Oracle 数据库的定时任务,在每天的某个时刻自动去执行我们所写的存储过程,这样就相对来说比较完美了。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4728字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中