Oracle 跨資料庫查詢與資料複製：Database Link 建立、同義詞應用與分散式查詢實戰

Oracle 跨資料庫查詢與資料複製：Database Link 建立、同義詞應用與分散式查詢實戰

企業常見需求：在資料庫 A 查詢/複製資料庫 B 的表資料。Oracle 透過 Database Link (DB Link) 實現分散式查詢。本文提供兩種建立 DB Link 方式 (本地 TNS + 完整描述符)、同義詞簡化存取、跨庫插入語法、DB Link 管理指令，以及密碼特殊字元處理技巧。

方法一：標準兩步驟 (推薦，易維護)

步驟 1：配置本地 tnsnames.ora 定義遠端連線描述符

# $ORACLE_HOME/network/admin/tnsnames.ora
MEDIADBLINK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db)
    )
  )

步驟 2：建立 Database Link 關聯描述符與認證

CREATE DATABASE LINK MEDIADB
  CONNECT TO dbuser IDENTIFIED BY password
  USING 'MEDIADBLINK';

方法二：單指令內嵌完整連線描述符 (適合臨時/一次性)

CREATE PUBLIC DATABASE LINK dblink_name
  CONNECT TO remote_user IDENTIFIED BY "password"
  USING '(DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME = orcl))
  )';

注意：密碼含特殊字元或數字開頭時，務必用雙引號包裹 (如 IDENTIFIED BY "1")。

3. 使用 DB Link 查詢與操作

-- 直接查詢遠端表 (表名@dblink)
SELECT * FROM employees@MEDIADB WHERE department_id = 10;

-- 建立同義詞簡化
CREATE SYNONYM remote_emp FOR employees@MEDIADB;
SELECT * FROM remote_emp WHERE department_id = 10;

-- 刪除同義詞
DROP SYNONYM remote_emp;

4. 跨庫複製資料 (INSERT ... SELECT)

-- 結構相同表直接複製
INSERT INTO local_table (col1, col2, col3)
SELECT col1, col2, col3 FROM remote_table@MEDIADB;

-- 條件複製
INSERT INTO local_sales
SELECT * FROM sales@MEDIADB WHERE sale_date >= DATE '2024-01-01';

5. DB Link 管理常用指令

6. 進階主題補充

• 表複製備份：CREATE TABLE t_bak AS SELECT * FROM t; (僅複製結構與資料，不含索引/約束/授權)。
• 分散式資料庫架構延伸：Oracle RAC (Shared Disk)、Sharding (Shared Nothing)、MPP 架構、表分區與並行查詢結合。
• 密碼特殊字元：CREATE USER aaa IDENTIFIED BY "1"; 雙引號保留大小寫與特殊符號。

7. 效能與安全建議

1. 遠端查詢大量資料時，考慮 DRIVING_SITE Hint 推算到遠端執行。
2. DB Link 認證建議使用專用唯讀帳號，避免高權限帳號跨庫。
3. 啟用 GLOBAL_NAMES=TRUE 強制 DB Link 名稱與全域資料庫名一致，減少誤連風險。
4. 監控 V$DBLINK、GV$SESSION 追蹤跨庫會話。

掌握 DB Link 建立、同義詞封裝、跨庫 DML 與管理指令，即可輕鬆實現 Oracle 異構/同構資料庫間的資料整合與查詢需求。