您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 與 SQL Server 跨資料庫查詢：Database Link 與 Linked Server 實作指南

編輯：Oracle 資料庫基礎

異構資料庫環境下，跨實例查詢是常見需求。Oracle 使用 Database Link (DB Link)，SQL Server 使用 Linked Server 實現遠端資料存取。本文分別說明建立連線、權限設定、查詢語法、常見錯誤與安全性建議，助您快速打通雙向資料通道。

Oracle Database Link 完整建立流程

1. 建立 Public Database Link (需 CREATE PUBLIC DATABASE LINK 權限)

CREATE PUBLIC DATABASE LINK linkname
  CONNECT TO scott IDENTIFIED BY sa
  USING '172.16.100.20:1521/ora10g20';

參數說明：

linkname：本地參考的連結名稱 (如 REMOTE_DB)。
CONNECT TO ... IDENTIFIED BY：遠端資料庫的認證帳密 (固定帳號模式)。
USING '...'：TNS 連線描述字串，可為 tnsnames.ora 中的別名、Easy Connect 字串 (host:port/service_name) 或完整 (DESCRIPTION=...) 格式。

2. 透過 DB Link 查詢遠端物件

-- 查詢遠端 scott 綱要下的表
SELECT * FROM emp@linkname;

-- 跨資料庫 JOIN (本地 dept 與遠端 emp)
SELECT d.dname, e.ename
FROM   dept d, emp@linkname e
WHERE  d.deptno = e.deptno;

3. 進階：Current User Database Link (免存帳密)

CREATE PUBLIC DATABASE LINK linkname
  CONNECT TO CURRENT_USER
  USING 'tns_alias';

使用當前登入使用者的身分連線遠端，需配合 Enterprise User Security (EUS) 或 SSL/Proxy Authentication，安全性較高。

SQL Server Linked Server 建立與查詢

1. 建立 Linked Server (T-SQL)

-- 基本建立 (SQL Server 到 SQL Server)
EXEC sp_addlinkedserver
  @server = 'SQLSRV3',           -- 本地邏輯名稱
  @srvproduct = N'SQL Server',   -- 產品名稱
  @provider = N'SQLNCLI',        -- 提供者 (SQL Native Client)
  @datasrc = N'192.168.1.50';    -- 遠端 IP/主機名

-- 建立登入對應 (本地 login -> 遠端 login)
EXEC sp_addlinkedsrvlogin
  @rmtsrvname = 'SQLSRV3',
  @useself = N'FALSE',           -- 不使用當前登入身分
  @locallogin = NULL,            -- NULL 表所有本地登入
  @rmtuser = 'remote_user',      -- 遠端帳號
  @rmtpassword = 'remote_pwd';   -- 遠端密碼

2. 四段式命名查詢

SELECT *
FROM   SQLSRV3.RemoteDB.dbo.Employees;  -- [LinkedServer].[Database].[Schema].[Table]

3. 異構查詢：SQL Server 連 Oracle (需 Oracle Provider for OLE DB)

EXEC sp_addlinkedserver
  @server = 'ORA_LINK',
  @srvproduct = 'Oracle',
  @provider = 'OraOLEDB.Oracle',
  @datasrc = 'ORCL_TNS_ALIAS';  -- tnsnames.ora 別名

EXEC sp_addlinkedsrvlogin 'ORA_LINK', 'FALSE', NULL, 'scott', 'tiger';

SELECT * FROM ORA_LINK..SCOTT.EMP;  -- Oracle 無 Database 概念，中間留空

雙向比較與最佳實務

特性	Oracle DB Link	SQL Server Linked Server
建立權限	CREATE DATABASE LINK / CREATE PUBLIC DATABASE LINK	sysadmin / sp_addlinkedserver
認證模式	固定帳號 / CURRENT_USER / GLOBAL	本地登入對應 / 無對應 / 自我對應
查詢語法	`table@dblink`	四段式 `Server.DB.Schema.Table`
交易支援	分散式交易 (2PC) 內建支援	需啟用 MSDTC，支援分散式交易
效能優化	DRIVING_SITE Hint、遠端過濾下推	OPENQUERY 下推、統計資料同步

安全性與維運建議

最小權限原則：遠端連線帳號僅授予 SELECT 或必要 DML 權限，嚴禁 DBA 權限。
加密傳輸：Oracle 啟用 SQLNET.ENCRYPTION_SERVER = REQUIRED；SQL Server 強制 Linked Server 使用 SSL/TLS。
網路隔離：DB Link / Linked Server 流量限制在專用管理網段，防火牆僅開放 1521 / 1433 埠。
監控與審計：定期稽核 DBA_DB_LINKS / sys.servers，移除未使用連結。
效能陷阱：避免大量資料跨 Link JOIN，建議先 INSERT INTO local_temp SELECT ...@remote 落地再 JOIN，或使用 OPENQUERY / DRIVING_SITE 強制遠端過濾。

常見錯誤快速排查

ORA-12154 / ORA-12541： TNS 別名錯誤、Listener 未啟動、防火牆阻擋。用 tnsping 驗證連通性。
ORA-02019： 遠端資料庫版本不支援該功能 (如舊版不支援特定資料型別)。
SQL Server 7399 / 7303： OLE DB Provider 未安裝/版本不符、遠端權限不足、查詢逾時。檢查 Provider 字串與遠端防火牆。

無論 Oracle DB Link 或 SQL Server Linked Server，核心精神都是「定義一次連線，隨處參考遠端物件」。善用綁定變數、遠端過濾下推與最小權限，即能安全高效地實現跨資料庫整合。