Oracle 或 SqlServer DbLink的创建流程说明

在实际开发过程中，我们会遇到不同的服务器上Oracle数据库或SqlServer数据库多表关联数据的情况，也就是所谓的跨库查询，为了解决这个问题需要对两个数据库创建连接，也就是所谓的DbLink连接，解决这个问题。

针对于Oracle与Oracle之间,通过Sql语句实现创建DbLink:

--查看BDCBZB用户是否具备创建database link 权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='BDCBZB';
--如果查询有返回行，则表示具备创建database link权限，否则，则需要使用sys登陆orcl为WANGYONG用户赋予创建权限
--给BDCBZB用户授予创建dblink的权限 
grant create public database link to BDCBZB;  
--ORCL:select * from global_name;(可以查询数据库名称）
--创建DbLink：注意一点，如果密码是数字开头，用“”括起来
--create public database link DbLink的名称 connect to 用户名 identified by 密码 USING '数据库'
create public database link Bblink_Wwsj connect to bdcbzb identified by bdcbzb using '192.168.27.8/ORCL'; 

或者采用tnsnames.ora里的连接配置：
create public database link DbLink_Wwsj    
 connect to bdcbzb identified by bdcbzb
 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.27.8)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCL)))'; 

 
--查询 select * FROM 表名@Bblink的名称; --查询业务库的数据
select * FROM BDC_PROGRESS@Bblink_Wwsj; 
--删除
Drop public DATABASE LINK Dblink_Wwsj;
--创建和删除同义词
--create or replace synonym 同义词名 for 表名;  
--create or replace synonym 同义词名 for 用户.表名;  
--create or replace synonym 同义词名 for 表名@数据库链接名;  
--drop synonym 同义词名;  
create or replace synonym Db_Wwsj_BDC_PROGRESS for BDC_PROGRESS@Bblink_Wwsj;
select * from Db_Wwsj_BDC_PROGRESS;
--创建和删除视图
--create or replace view V_Db_Wwsj_BDC_PROGRESS as (select * from BDC_PROGRESS@Bblink_Wwsj);  
--select * from V_Db_Wwsj_BDC_PROGRESS;
--drop view V_Db_Wwsj_BDC_PROGRESS; 
对DbLink的命名规范：  
DbLin的名称(Dblink_外网收件)：Dblink_Wwsj
取同义词名称(Db_Wwsj_要推送的表)：Db_Wwsj_BDC_PROGRESS

针对于SqlServer与SqlServer之间，通过SqlServer创建DbLink:

此方法不需要在外部创建DbLink

现在假设服务A，数据库B 帐号C 密码D 表Table_F
现在假设服务A1，数据库B1 帐号C1 密码D1 表Table_F1
现在假设服务A2，数据库B2 帐号C2 密码D2 表Table_F2

需要在服务器A的数据库B上访问得到所有Table_F、Table_F1、Table_F2的所有数据，

创建视图V1

create View V1
as 
(
 select
    *
from opendatasource
(
    'SQLOLEDB',
    'Data Source=A1;User ID=C1;Password=D1'
).B1.dbo.Table_F1 as t1

)

创建视图V2

create View V2
as 
(
 select
    *
from opendatasource
(
    'SQLOLEDB',
    'Data Source=A2;User ID=C2;Password=D2'
).B2.dbo.Table_F2 as t1
)

关联后得到数据库sql语句:
select * from Table_F
union
select * from V1
union
select * from V2

这样就能实现多跨服务器、跨库多表联查了

此方法需要创建DbLink：

EXEC sp_addlinkedserver 
@server='YSB8',--被访问的服务器别名(随便定义)
@srvproduct='', 
@provider='SQLOLEDB', 
@datasrc="192.168.27.8" --要访问的服务器 

EXEC sp_addlinkedsrvlogin 
'YSB8', --被访问的服务器别名 (和上面de @server匹配)
'false', 
NULL, 
'sa', --帐号 
'sasa' --密码 

使用实例： 
Select * from YSB8.BDCBZB2.dbo.ESTATEINTERACTIVE