Oracle 批量匯出使用者所有表：SPOOL 動態生成 EXP 腳本實戰

單一使用者全表匯出自動化腳本生成技巧

當需匯出特定 Schema 下所有表時，逐一撰寫 EXP 命令極其繁瑣。利用 SQL*Plus 的 SPOOL 功能動態生成批次檔，可一鍵完成全表匯出，大幅提升運維效率。

核心思路

1. 登入目標使用者 Schema
2. 關閉回饋、頁首、驗證，設定寬度與裁剪
3. 查詢 USER_TABLES 產生每張表的 EXP 命令列
4. 輸出至 .bat 檔，編輯移除首尾雜訊後執行

完整 SQL*Plus 腳本

SET FEEDBACK OFF
SET PAGESIZE 0
SET HEADING OFF
SET VERIFY OFF
SET LINESIZE 200
SET TRIMSPOOL ON

SPOOL c:\export_all_tables.bat

SELECT 'EXP username/password@yourdb ' ||
       'TABLES=' || table_name || ' ' ||
       'FILE=' || table_name || '.dmp ' ||
       'TRIGGERS=N'
FROM   user_tables;

SPOOL OFF

SET FEEDBACK ON
SET PAGESIZE 9999
SET HEADING ON
SET VERIFY ON
EXIT

生成的批次檔範例

EXP scott/tiger@ORCL TABLES=EMP FILE=EMP.dmp TRIGGERS=N
EXP scott/tiger@ORCL TABLES=DEPT FILE=DEPT.dmp TRIGGERS=N
EXP scott/tiger@ORCL TABLES=BONUS FILE=BONUS.dmp TRIGGERS=N
...

關鍵參數說明

• TRIGGERS=N：不匯出觸發器，避免匯入時衝突；若需保留請改 Y
• FILE=表名.dmp：每表獨立檔案，便於單表恢復與平行處理
• 建議加上 LOG=表名.log 記錄各表匯出狀態

進階優化建議

1. 並行化：將批次檔拆分為 N 份，同時執行利用多核心 CPU
2. 改用 Data Pump：10g+ 環境建議生成 EXPDP 命令，支援並行度 (PARALLEL=4)、壓縮 (COMPRESSION=ALL)、網路模式直傳
3. 加入檢查點：腳本前檢查磁碟空間、匯出目錄權限、資料庫連線狀態
4. 錯誤處理：批次檔中加入 IF ERRORLEVEL 1 GOTO FAIL 記錄失敗表名

適用場景與限制

此技巧為傳統 EXP 時代的經典自動化手法，現代環境請優先考慮 Data Pump，但理解其動態 SQL 生成邏輯對掌握資料庫元資料驅動運維仍有參考價值。