Oracle 用戶全表導出腳本：SPOOL 動態生成 EXP 批次檔，一鍵備份 Schema 所有表

需快速導出某 Schema 下所有表時，手工寫 exp 命令極易遺漏。本文提供一條 SQL*Plus 腳本，利用 SPOOL 動態生成 Windows 批次檔，包含該用戶所有表的獨立 exp 命令，執行批次檔即可完成全 Schema 導出。

一、核心 SQL*Plus 腳本

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

SPOOL c:\full_schema_export.bat

SELECT 'exp ' || user || '/password@' || global_name ||
       ' TABLES=' || table_name ||
       ' FILE=' || table_name || '.dmp' ||
       ' LOG=' || table_name || '.log' ||
       ' TRIGGERS=N'
FROM   user_tables, global_name;

SPOOL OFF

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

EXIT

二、使用步驟

1. 以目標 Schema 使用者登入 SQL*Plus：sqlplus scott/tiger@orcl
2. 執行上述腳本（複製貼上或 @export_gen.sql）。
3. 開啟生成的 c:\full_schema_export.bat，刪除第一行（SPOOL ...）與最後一行（EXIT）。
4. 檢查每行 exp 命令參數（密碼、連線字串、TRIGGERS 選項）。
5. 雙擊或命令列執行 full_schema_export.bat，逐表導出 .dmp + .log。

三、進階增強版（支援並行、壓縮、排除系統表）

SET FEEDBACK OFF PAGESIZE 0 HEADING OFF VERIFY OFF LINESIZE 300 TRIMSPOOL ON
SPOOL c:\export_parallel.bat

SELECT 'expdp ' || user || '/password@' || global_name ||
       ' TABLES=' || table_name ||
       ' DUMPFILE=' || table_name || '_%U.dmp' ||
       ' LOGFILE=' || table_name || '.log' ||
       ' PARALLEL=4 COMPRESSION=METADATA_ONLY REUSE_DUMPFILES=Y'
FROM   user_tables
WHERE  table_name NOT LIKE 'BIN$%'      -- 排除回收站
AND    table_name NOT LIKE 'SYS_%'      -- 排除系統內部表
AND    table_name NOT IN (SELECT table_name FROM user_nested_tables); -- 排除巢狀表儲存表

SPOOL OFF
EXIT

• 改用 expdp（Data Pump）支援並行、壓縮、多檔案。
• %U 自動展開為 01, 02... 多檔案。
• 過濾回收站、系統內部表、巢狀表儲存表。

四、Linux/Unix 版本（Shell 腳本生成）

SET FEEDBACK OFF PAGESIZE 0 HEADING OFF VERIFY OFF LINESIZE 300 TRIMSPOOL ON
SPOOL /home/oracle/export_all.sh

SELECT 'expdp ' || user || '/password@' || global_name || ' \\' || CHR(10) ||
       '  TABLES=' || table_name || ' \\' || CHR(10) ||
       '  DUMPFILE=' || table_name || '_%U.dmp \\' || CHR(10) ||
       '  LOGFILE=' || table_name || '.log \\' || CHR(10) ||
       '  PARALLEL=4 COMPRESSION=ALL REUSE_DUMPFILES=Y'
FROM   user_tables
WHERE  table_name NOT LIKE 'BIN$%';

SPOOL OFF
-- 後續在 OS 執行：chmod +x export_all.sh && ./export_all.sh

五、注意事項與最佳實務

六、單命令導出全 Schema（最簡方式）

若不需單表獨立 .dmp，直接一條命令最快：

# exp (舊版)
exp scott/tiger@orcl OWNER=scott FILE=scott_full.dmp LOG=scott_full.log

# expdp (10g+ 推薦)
expdp scott/tiger@orcl SCHEMAS=scott DUMPFILE=scott_%U.dmp LOGFILE=scott.log PARALLEL=8 COMPRESSION=ALL

動態生成批次檔適合「需單表獨立備份、後續可能單表還原」場景；整體 Schema 導出用 expdp SCHEMAS=... 更簡潔高效。根據恢復粒度需求選擇策略，將導出作業標準化、自動化。