Oracle 匯入異構結構表：DB Link INSERT、SQL*Loader 與 Data Pump 重映射三大方案

場景定義

來源表 SRC.TABLE_A (col1, col2, col3) → 目標表 TGT.TABLE_A (col1, col2, col3, col4, col5)，目標多兩欄 col4, col5 需匯入為 NULL/預設值。

方案一：Database Link + INSERT SELECT (即時同步)

-- 1. 建立 DB Link (目標端執行)
CREATE DATABASE LINK src_link
  CONNECT TO src_user IDENTIFIED BY pass
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=src_host)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=src_svc)))';

-- 2. 插入指定欄位，新欄位自動為 NULL/DEFAULT
INSERT INTO tgt_table_a (col1, col2, col3)
SELECT col1, col2, col3 FROM src_table_a@src_link
WHERE create_dt > SYSDATE - 1;

-- 3. 批次提交大量資料
DECLARE
  CURSOR c IS SELECT col1, col2, col3 FROM src_table_a@src_link WHERE ...;
  TYPE arr_t IS TABLE OF c%ROWTYPE;
  l_arr arr_t;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_arr LIMIT 10000;
    FORALL i IN 1..l_arr.COUNT
      INSERT INTO tgt_table_a (col1, col2, col3) VALUES (l_arr(i).col1, l_arr(i).col2, l_arr(i).col3);
    COMMIT;
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END;
/
COMMIT;

優點：即時、可篩選、可轉換；缺點：跨網路、大量資料較慢。

方案二：SQL*Loader (離線檔案載入)

來源端匯出扁平檔

-- SQL*Plus
SET COLSEP ',' PAGESIZE 0 FEEDBACK OFF TRIMSPOOL ON
SPOOL table_a.csv
SELECT col1 || ',' || col2 || ',' || col3 FROM src_table_a WHERE ...;
SPOOL OFF

目標端控制檔

LOAD DATA
INFILE 'table_a.csv'
APPEND
INTO TABLE tgt_table_a
FIELDS TERMINATED BY ','
(
  col1,
  col2,
  col3,
  col4  CONSTANT "NULL",      -- 固定 NULL
  col5  EXPRESSION "SYSDATE"  -- 或 DEFAULT
)

優點：高效能、離線、可轉換；缺點：需中間檔案儲存空間。

方案三：Data Pump 匯出/匯入 + 重映射 (企業級標準)

來源端匯出

expdp src_user/pass@SRC DIRECTORY=dp_dir
  DUMPFILE=table_a.dmp TABLES=TABLE_A
  QUERY=TABLE_A:"WHERE create_dt > SYSDATE-1"
  LOGFILE=exp_table_a.log

目標端匯入 (重映射欄位)

-- 1. 建立暫存表對應來源結構
CREATE TABLE tmp_table_a AS SELECT * FROM src_table_a@src_link WHERE 1=0;

-- 2. 匯入至暫存表
impdp tgt_user/pass@TGT DIRECTORY=dp_dir
  DUMPFILE=table_a.dmp TABLES=TABLE_A
  REMAP_TABLE=TABLE_A:TMP_TABLE_A
  TABLE_EXISTS_ACTION=REPLACE

-- 3. 轉換插入目標表
INSERT INTO tgt_table_a (col1, col2, col3, col4, col5)
SELECT col1, col2, col3, NULL, SYSDATE FROM tmp_table_a;

-- 4. 清理
DROP TABLE tmp_table_a;

進階：Data Pump TRANSFORM 參數 (12c+)

impdp ... TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE
  REMAP_TABLE=TABLE_A:TGT_TABLE_A
  -- 但無法直接處理欄位數不符，需搭配上述暫存表

方案對比選型

最佳實踐建議

1. 常態同步：DB Link + 批次 PL/SQL (配合 DBMS_SCHEDULER 排程)
2. 大量歷史遷移：Data Pump + 暫存表轉換 (平行、壓縮、可重啟)
3. 異構平台/版本：Data Pump NETWORK_LINK 直接跨版本匯入
4. 欄位預設值：目標表定義 DEFAULT，匯入時不指定欄位即自動套用