共计 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 数据则说明创建成功,反之就是不存在。
第五步:测试建立的远程数据库链接
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 数据库的定时任务,在每天的某个时刻自动去执行我们所写的存储过程,这样就相对来说比较完美了。