Oracle Export/Import (EXP/IMP) 工具完整使用指南

Oracle Export/Import 工具概述

Oracle Export (EXP) 和 Import (IMP) 是 Oracle 資料庫早期版本中最核心的邏輯備份與遷移工具。雖然 Oracle 10g 後推出了功能更強大的 Data Pump (expdp/impdp)，但 EXP/IMP 仍廣泛用於舊版本資料庫維護、跨版本遷移及簡單的資料導出入場景。本文系統整理其核心概念、操作流程、參數詳解、常見問題及實戰腳本。

1. 核心用途與適用場景

• 跨資料庫資料傳輸：支援同版本、不同版本、同平台、跨平台（如 Windows 到 Linux）的資料移動。
• 邏輯備份與恢復：以二進制 Dump 檔案形式保存資料，作為物理備份的補充。
• Schema/表空間級遷移：支援從一個 Schema 導入另一個 Schema（FROMUSER/TOUSER），或跨表空間重組（TRANSPORT_TABLESPACE）。
• 碎片整理：通過導出導入重建表，消除行遷移/鏈接，壓縮儲存空間。

2. Dump 檔案特性

• 二進制專有格式，嚴禁手工編輯，否則導致資料損壞。
• 跨平台通用：同一 Dump 檔可在 Oracle 支援的任何作業系統上導入。
• 向上相容：低版本導出的 Dump 可導入高版本資料庫（如 7.3 → 8.1.7），但跨大版本（如 8i → 11g）可能遇到字典視圖不相容問題。

3. 導出/導入流程與物件包含範圍

Dump 檔包含兩類核心內容：

• DDL (Data Dictionary Language)：重建資料字典的 SQL 語句（可讀但不可修改）。
• Data：實際資料列。

三種導出模式包含的物件差異：

4. Import 物件導入順序（依賴關係處理）

IMP 遵循固定順序解決物件依賴，典型順序如下（版本略有差異）：

1. Tablespaces
2. Profiles, Users, Roles, System Privileges
3. Tables (含資料、索引、約束、授權)
4. Referential Integrity (外鍵)
5. Synonyms, Views, Procedures, Functions, Packages
6. Triggers (最後導入，避免資料載入時觸發)

注意：導入後可能出現 INVALID 狀態的 Procedure/Function，需手動重新編譯（`ALTER PROCEDURE ... COMPILE`）。

5. 版本相容性與 CATEXP.SQL 角色

• IMP 可讀取 EXP 5.1.22 之後版本產生的 Dump。
• 導出需依賴 `CATEXP.SQL` 建立的內部字典視圖，不同版本視圖不同。
• 跨版本導出導入標準流程：
  • 源版本 < 目標版本：目標庫執行舊版 CATEXP.SQL → 舊版 EXP 導出 → 舊版 IMP 導入 → 目標庫執行新版 CATEXP.SQL 恢復視圖。
  • 源版本 > 目標版本：目標庫執行新版 CATEXP.SQL → 新版 EXP 導出 → 新版 IMP 導入 → 目標庫執行舊版 CATEXP.SQL 恢復視圖。

6. 關鍵參數 COMPRESS 的陷阱與碎片整理

`COMPRESS=Y`（預設）會將表的 `INITIAL` 儲存參數設為「所有 Extent 總大小」，而非實際資料大小。

風險案例：

• 表 A 有 4 個 100M Extent，刪除所有資料後 `COMPRESS=Y` 導出，產生 `INITIAL=400M` 的建表語句。導入時會嘗試分配 400M 連續空間，即使表為空也會失敗（若資料檔小於 400M）。
• 多資料檔表空間中，單一 Extent 不能跨檔案，導致導入報錯 `ORA-01658`。

建議：大表整理時使用 `COMPRESS=N`，或先 `ALTER TABLE ... MOVE` 整理後再導出。

7. Schema 與表空間間資料遷移技巧

• Table/User 模式導出依賴目標用戶預先存在，否則報錯。
• Full 模式導出包含 `CREATE USER`，可自動建立用戶。
• 跨 Schema 導入必須使用 `FROMUSER=src TOUSER=target`，且目標用戶必須已存在且有配額。
• 跨表空間導入：結合 `TRANSPORT_TABLESPACE` 或導入前修改用戶預設表空間。

8. Sequence 在 EXP/IMP 中的行為

• 導出時若用戶正在取值，可能導致 Sequence 不一致。
• 使用 `CACHE` 時，快取中未持久化的值會丟失，導入後從字典值開始，造成間隙。
• Full 模式導出導入若遇到觸發器使用 `sequence.nextval`，直接路徑 (`DIRECT=Y`) 可能不觸發觸發器，影響 Sequence 值。

9. EXP/IMP 核心參數速查表

Export (EXP) 常用參數

exp userid=scott/tiger \
    file=exp_$(date +%Y%m%d).dmp \
    log=exp_$(date +%Y%m%d).log \
    owner=scott \
    buffer=4096000 \
    rows=n \
    compress=n \
    consistent=y \
    statistics=none

Import (IMP) 常用參數

imp userid=scott/tiger \
    file=exp_20231001.dmp \
    log=imp_20231001.log \
    fromuser=scott \
    touser=scott_new \
    ignore=y \
    indexes=y \
    constraints=y \
    grants=y \
    rows=y \
    commit=y \
    buffer=4096000

10. 實戰自動化備份腳本範例 (Linux/Unix)

範例 1：Schema 級每日完整導出 + 單表導出 + 壓縮

#!/bin/bash
# 環境變數設定 (crontab 執行必須)
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19c
export ORACLE_SID=orcl
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH

BACKUP_DIR=/backup/oracle/exp
USER_PASS=scott/tiger
LOG_DIR=$BACKUP_DIR/logs
mkdir -p $BACKUP_DIR $LOG_DIR

cd $BACKUP_DIR
DATE_TAG=$(date +%Y%m%d)

# 1. Schema 完整導出 (不含資料，僅結構)
exp $USER_PASS file=schema_${DATE_TAG}.dmp log=schema_${DATE_TAG}.log owner=scott rows=n compress=n consistent=y

# 2. 逐表導出資料 (直接路徑加速)
sqlplus -s $USER_PASS <<EOF
set heading off feedback off pagesize 0 trimspool on
spool table_list.lst
select table_name from user_tables;
spool off
EOF

for tbl in $(cat table_list.lst); do
  exp $USER_PASS file=${tbl}_${DATE_TAG}.dmp log=${tbl}_${DATE_TAG}.log tables=$tbl direct=y rows=y
done

# 3. 壓縮所有 Dump
gzip -f *.dmp
echo "$(date) Backup completed" >> $LOG_DIR/backup_result.log

範例 2：異地備份 - 結合 FTP 自動傳輸

# .netrc 檔案 (置於 $HOME，權限 600)
machine backup-server.example.com
login oracle_bak
password ********
macdef init
binary
lcd /backup/oracle/exp
cd /remote/backup/oracle
mput *.gz
bye

# crontab -e (oracle 用戶)
# 每天 23:10 執行導出，23:30 啟動 FTP 傳輸
10 23 * * * /home/oracle/scripts/exp_full.sh
30 23 * * * ftp -i backup-server.example.com

11. 常見錯誤與排查

12. 最佳實踐總結

1. 版本對齊：永遠使用「源/目標資料庫中較低版本」的 EXP，目標版本的 IMP。
2. 一致性：生產庫導出務必加 `CONSISTENT=Y` (或使用 `FLASHBACK_SCN`/`FLASHBACK_TIME`)，避免跨表不一致。
3. 大表優化：`DIRECT=Y` + `BUFFER` 加大 + `RECORDLENGTH` 設為 64KB + 並行導出多個 Dump (`FILESIZE`)。
4. 導入加速：`COMMIT=Y` + `INDEXES=N` (先導資料後建索引) + `CONSTRAINTS=N` (後啟用約束) + 關閉歸檔/觸發器。
5. 驗證機制：導入後執行 `ANALYZE` 或 `DBMS_STATS`，比對源端表行數、約束狀態、索引有效性。

雖然 Data Pump (expdp/impdp) 在並行度、網路模式、細粒度過濾等方面全面超越 EXP/IMP，但後者因其簡單、相容性極佳、無需 Directory Object、支援極舊版本 (7.3+)，在「異構舊版本遷移」、「輕量級邏輯備份」、「無共享儲存環境下的跨機器拷貝」等場景仍具不可替代價值。掌握其參數細節與陷阱，是資料庫管理員的基本功。