【獨孤九劍】Oracle 實戰 (三) 匯出匯入 (exp/imp & expdp/impdp) 完整操作手冊

【獨孤九劍】Oracle 實戰 (三) 匯出匯入 (exp/imp & expdp/impdp) 完整操作手冊

資料遷移、備份還原、環境複製的核心工具。10g 前使用傳統 exp/imp (客戶端執行)；10g+ 推薦 Data Pump expdp/impdp (伺服器端並行、高效能、支援網路導入)。

1. 傳統 exp/imp (9i/10g 相容)

匯出範例

-- 全庫匯出
exp user/pass@orcl FILE=d:\backup\full.dmp LOG=d:\backup\full.log FULL=Y

-- 指定 Schema
exp user/pass@orcl FILE=d:\backup\schemas.dmp LOG=... OWNER=(user1,user2)

-- 指定表
exp user/pass@orcl FILE=... TABLES=(t1,t2)

-- 查詢條件匯出 (WHERE 子句)
exp user/pass@orcl FILE=... TABLES=(t1) QUERY="WHERE col LIKE '00%'"

匯入範例

-- 全庫匯入
imp user/pass@orcl FILE=d:\backup\full.dmp LOG=... FULL=Y

-- 忽略已存在表 (IGNORE=Y)
imp user/pass@orcl FILE=... FULL=Y IGNORE=Y

-- 同名使用者間表級匯入
imp user/pass@orcl FILE=... TABLES=(t1)

-- 跨使用者匯入 (FROMUSER → TOUSER)
imp user/pass@orcl FILE=... FROMUSER=userA TOUSER=userB

2. Data Pump expdp/impdp (10g+ 推薦)

前置步驟 (一次設定)：

-- 1. 建立目錄物件 (指向資料庫伺服器端路徑)
CREATE DIRECTORY dp_dir AS 'D:\backup\dp_dir';

-- 2. 授權
GRANT READ, WRITE ON DIRECTORY dp_dir TO username;

-- 3. 查看目錄權限
SELECT privilege, directory_name, directory_path
  FROM user_tab_privs t, all_directories d
 WHERE t.table_name(+) = d.directory_name;

匯出模式與範例

關鍵注意事項

• DIRECTORY 參數必須置於最前，否則報 ORA-39070/ORA-39087。
• 目錄路徑必須在資料庫伺服器端，非客戶端。
• 指令勿加分號，否則 JOB_NAME 會包含分號導致 attach 失敗。
• 指定 JOB_NAME 便於監控與重啟 (expdp ... ATTACH=job_name)。

匯入模式與範例

3. 跨版本匯入匯出 (版本參數 VERSION)

-- 11g → 10g 匯出
expdp ... VERSION=10.2.0.1.0

-- 10g → 11g 匯入
impdp ... VERSION=10.2.0.1.0

急救技巧： 用 Notepad++ 開啟 .dmp 檔，修改檔頭版本號 (11gR2: V11.02.00, 11gR1: V11.01.00, 10g: V10.02.01) 可嘗試強制相容，但非官方支援，需謹慎驗證。

4. 常用 emca 維護指令 (Enterprise Manager)

emca -repos create|recreate|drop
emca -config dbcontrol db
emca -deconfig dbcontrol db
emca -reconfig ports -DBCONTROL_HTTP_PORT 1160
emctl start|stop|status dbconsole