Oracle資料庫備份恢復實戰(二)：SQL*Loader高效載入與Shell自動化腳本

SQL*Loader 是 Oracle 高效載入外部格式化文字資料的核心工具，常與 SPOOL 導出配合實現資料遷移、ETL、備份恢復。本文詳細解析控制檔語法、資料格式、常規/直接路徑模式、SPOOL 導出技巧，並提供生產級 Shell 自動化腳本。

一、核心概念與命令語法

sqlldr userid=user/pwd control=emp.ctl data=emp.dat bad=emp.bad log=emp.log

控制檔三段式結構

1. 全域選項： `LOAD DATA`、`INFILE`、`BADFILE`、`DISCARDFILE`、`ROWS`、`BINDSIZE`、`READSIZE`、`SKIP` 等
2. INFILE 子句： 指定輸入資料來源（檔案或內嵌 `BEGINDATA`）
3. 資料描述： `INTO TABLE`、欄位定義、終止符、資料型別轉換、日期格式、填充欄位等

控制檔範例

LOAD DATA
INFILE *
APPEND
INTO TABLE emp
FIELDS TERMINATED BY '|'
(
  no      FLOAT EXTERNAL,
  name    CHAR(20),
  age     INTEGER EXTERNAL,
  duty    CHAR(1),
  salary  FLOAT EXTERNAL,
  upd_ts  DATE(14) "YYYYMMDDHH24MISS"
)
BEGINDATA
100000000003|Mulder|000020|1|000000005000|20020101000000
100000000004|Scully|000025|2|000000008000|20020101235959

二、三種資料檔格式

三、載入模式對比

四、SPOOL 導出文字資料最佳實務

SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0
SET TRIMOUT ON TRIMSPOOL ON LINESIZE 2500
-- LINESIZE 足夠大防截斷，TRIMSPOOL 移除尾部空格
-- Windows 下建議用 CMD 執行 SQL*Plus 最小化視窗，避免 PL/SQL Developer 導出緩慢
SPOOL /path/export.dat
SELECT col1||'|'||col2||'|'||col3 FROM table_name;
SPOOL OFF

注意： 包含換行符的欄位需預先清洗/轉義，確保單行一筆記錄。

五、生產級自動化 Shell 腳本 (KSH)

1. unloadtable — 表資料導出為 | 分隔 .dat 檔

#!/bin/ksh
# 用法: unloadtable table_name username password
if [ $# -ne 3 ]; then echo "usage: unloadtable tablename username password"; exit 1; fi

TBL=$1; USR=$2; PWD=$3
TMP_COL=/tmp/${TBL}.col
TMP_SEL=/tmp/${TBL}.sel

# 1. 產生欄位清單 SQL
cat > ${TMP_COL} < ${TMP_SEL} < ${TBL}_tmp.dat
awk '{print $0}' ${TBL}_tmp.dat > ${TBL}.dat
rm -f ${TBL}_tmp.dat ${TMP_COL} ${TMP_SEL}

2. loadtable — .dat 檔載入對應表

#!/bin/ksh
# 用法: loadtable table_name username password
if [ $# -ne 3 ]; then echo "usage: loadtable tablename username password"; exit 1; fi

TBL=$1; USR=$2; PWD=$3
TMP_COL=/tmp/${TBL}.colsql
TMP_CTL=/tmp/${TBL}.ctl
TMP_SQL=/tmp/${TBL}.sql

# 1. 取得欄位清單
cat > ${TMP_COL} < ${TMP_CTL} <> ${TMP_CTL}

# 3. 清空目標表並載入
echo "TRUNCATE TABLE ${TBL};" > ${TMP_SQL}
sqlplus ${USR}/${PWD} @${TMP_SQL}
sqlldr ${USR}/${PWD} DATA=${TBL}.dat CONTROL=${TMP_CTL} LOG=/tmp/${TBL}.log

# 清理暫存檔
rm -f ${TMP_COL} ${TMP_CTL} ${TMP_SQL}

六、效能調優關鍵參數

• BINDSIZE / READSIZE： 設為 10MB-100MB (視記憶體)，成對使用，較大者生效
• ROWS： 常規路徑下控制 Commit 頻率，建議 1000-10000
• DIRECT=TRUE： 大數據量首選，搭配 `UNRECOVERABLE` 進一步減少 Redo (非生產環境)
• 平行載入： 多個 SQL*Loader 併行載入不同分區/表，配合 `PARALLEL=TRUE`
• 檔案系統： 輸出檔置於本地高速磁碟，避免 NFS