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

什么是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数据库的定时任务,在每天的某个时刻自动去执行我们所写的存储过程,这样就相对来说比较完美了。

相关推荐