Oracle&SQLServer中實現跨庫查詢

標籤：預設   AC   linked   資料表   strong   安裝   hot   監聽   問題   

一、在SQLServer中串連另一個SQLServer庫資料

在SQL中，要想在本地庫中查詢另一個資料庫中的資料表時，可以建立一個連結的伺服器：

EXEC master.dbo.sp_addlinkedserver @server = N‘別名‘, @srvproduct=N‘庫名‘,@provider=N‘SQLOLEDB‘, @datasrc=N‘伺服器位址‘

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N‘別名‘, @locallogin = NULL ,@useself = N‘False‘, @rmtuser = N‘使用者名稱‘, @rmtpassword = N‘密碼‘

建立完後，就可以通過“Select * from別名.庫名.dbo.表名”來查詢了。

或者也可以手工建立：

 

 

 

 

二、在Oracle中串連另一個Oracle庫資料

在Oracle中，其實也類似，要串連到其他庫時，也需要建立一個類似這樣的串連：

create database link 別名 connect to 模式名（使用者名稱） identified by "密碼" using ‘TNS名‘;

注意：這裡面的TNS名就是你需要串連的另一個庫的TNS名，而且是必需是在你當前串連的庫的伺服器端所配置的TNS名。

建立完後，我們也就可以訪問了：“Select * from表名@別名”

如果使用的是PL/SQL開發工具，那麼我們也可以直接在工具裡建立：

 

 

 

 

三、在SQL Server中串連Oracle資料

同樣，也建立一個資料庫連接即可，這時我們採用Ole DB方式串連資料庫：

EXEC master.dbo.sp_addlinkedserver @server = N‘別名‘, @srvproduct=N‘庫名‘,@provider=N‘MSDAORA‘, @datasrc=N‘TNS名‘

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N‘別名‘, @locallogin = NULL ,@useself = N‘False‘, @rmtuser = N‘模式名‘, @rmtpassword = N‘密碼‘

注意：這裡面的TNS名，是在該SQL Server器端所配置的TNS名，不是在用戶端本地哦。

建立好了後，使用“select * from openquery(別名,‘select * from模式名.表名”來執行查詢。

要串連到其他類型的資料庫時，其實方式也類同，只要用相應的provider來串連即可。

 

四、在Oracle中串連SQL Server資料

在oracle中串連SQLServer也很類似，建立一個DBLink，但問題是，建立DBLink裡，裡面用的TNS名稱都是串連到Oracle的，沒有配置串連到SQL Server中的。

於是想到採用Oracle中的透明網關來實現，首先在Oracle的安裝名中裝上，Oracle Net Services和Oracle Transparent Gateways, 並在此項下選擇Oracle Transparent Gateway for Microsoft SQL Server。

配置透明網關，編輯%ORACLE_HOME%/tg4msql/admin/init%ORACLE_SID%.ora, 該檔案包含了TG for SQL Server的配置資訊, 其中%ORACLE_SID%是給TG的"SID", 預設為tg4msql. 修改檔案中的行HS_FDS_CONNECT_INFO="SERVER=SQL伺服器位址;DATABASE=庫名"。

然後建立監聽器：編輯%ORACLE_HOME%/network/admin/listener.ora, 編輯對應listener的SID_LIST：

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC= 
(SID_NAME=%ORACLE_SID%)
(ORACLE_HOME=oracle_home_directory)
(PROGRAM=tg4msql)
)
)

其中%ORACLE_SID%為第二布中設定的SID, 預設值為tg4msql. 修改listener.ora檔案後需重啟listener使修改生效.

最後就可以配置TNS名了，如果直接修改Tnsname.ora檔案的話，添加的格式是：

TNS名=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=sqlserver)(PORT=1521))
(CONNECT_DATA=(SID=網關ID))
(HS=OK))

這樣，TNS名後就可以建立DB Links，然後查詢的方式與前面一至。

Oracle&SQLServer中實現跨庫查詢