批次檔完整內容 (expdp_backup.bat)
@ECHO OFF
REM ==================================================
REM Oracle 11g expdp 自動備份腳本
REM 1. 檔名含日期：SCHEMA_YYYYMMDD.dmp
REM 2. 先導出至暫存目錄，再依年/月/日歸檔至永久目錄
REM 3. 排程：at 02:00 /every:m,t,w,th,f,s,su "D:\scripts\expdp_backup.bat"
REM 刪除排程：at 1 /delete
REM ==================================================
SET MYDATE=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%
SET YYYY=%DATE:~0,4%
SET MM=%DATE:~5,2%
SET DD=%DATE:~8,2%
SET TMPDIR=E:\app\Administrator\admin\zcjc\dpdump
SET BAKROOT=E:\expdp_backup
MKDIR %BAKROOT%\%YYYY% 2>NUL
MKDIR %BAKROOT%\%YYYY%\%MM% 2>NUL
MKDIR %BAKROOT%\%YYYY%\%MM%\%DD% 2>NUL
REM 導出 5 個 Schema
expdp ppp/ppp@zcjc DUMPFILE=zcjc_ppp_%MYDATE%_exp.dmp CONTENT=ALL
expdp aus/aus@zcjc DUMPFILE=zcjc_aus_%MYDATE%_exp.dmp CONTENT=ALL
expdp cif/cif@zcjc DUMPFILE=zcjc_cif_%MYDATE%_exp.dmp CONTENT=ALL
expdp spm/spm@zcjc DUMPFILE=zcjc_spm_%MYDATE%_exp.dmp CONTENT=ALL
expdp ua/ua@zcjc DUMPFILE=zcjc_ua_%MYDATE%_exp.dmp CONTENT=ALL
REM 搬移並清理暫存
COPY "%TMPDIR%\zcjc_ppp_%MYDATE%_exp.dmp" "%BAKROOT%\%YYYY%\%MM%\%DD%\"
COPY "%TMPDIR%\zcjc_aus_%MYDATE%_exp.dmp" "%BAKROOT%\%YYYY%\%MM%\%DD%\"
COPY "%TMPDIR%\zcjc_cif_%MYDATE%_exp.dmp" "%BAKROOT%\%YYYY%\%MM%\%DD%\"
COPY "%TMPDIR%\zcjc_spm_%MYDATE%_exp.dmp" "%BAKROOT%\%YYYY%\%MM%\%DD%\"
COPY "%TMPDIR%\zcjc_ua_%MYDATE%_exp.dmp" "%BAKROOT%\%YYYY%\%MM%\%DD%\"
DEL "%TMPDIR%\zcjc_ppp_%MYDATE%_exp.dmp"
DEL "%TMPDIR%\zcjc_aus_%MYDATE%_exp.dmp"
DEL "%TMPDIR%\zcjc_cif_%MYDATE%_exp.dmp"
DEL "%TMPDIR%\zcjc_spm_%MYDATE%_exp.dmp"
DEL "%TMPDIR%\zcjc_ua_%MYDATE%_exp.dmp"
EXIT
關鍵細節
%DATE% 格式視系統地區設定而異，上述切片適用 YYYY-MM-DD；若為 MM/DD/YYYY 需調整索引。CONTENT=ALL 導出結構+資料；若僅需結構改 METADATA_ONLY。- 暫存目錄
TMPDIR 為 DATA_PUMP_DIR 指向路徑，導出完成後立即搬移釋放空間。
- 排程建議使用 Windows 工作排程器（比
at 更穩定，支援登入觸發、錯誤重試、日誌）。
進階：加入日誌與錯誤通知
>> "%BAKROOT%\expdp_%MYDATE%.log" 2>&1 (
expdp ...
)
IF %ERRORLEVEL% NEQ 0 (
powershell -Command "Send-MailMessage -To 'dba@company.com' -Subject 'expdp 失敗' -Body '請檢查日誌' -SmtpServer smtp.company.com"
)
