什么是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 HSAJ216connect to hs_user identified by hundsunusing '(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数据则说明创建成功，反之就是不存在。

 

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

select * from dual@HSAJ216;

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

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

select * from hs_asset.client@HSAJ216;

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

 

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

/***从柜台同步客户数据至临时表*/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数据库的定时任务，在每天的某个时刻自动去执行我们所写的存储过程，这样就相对来说比较完美了。

 

结束语：为了不让生活留下遗憾和后悔，我们应该尽可能抓住一切改变生活的机会。

 

可爱博主：AlanLee

博客地址：

本文出自博客园，欢迎大家加入博客园。