SQL*Plus SPOOL 命令是將查詢結果導出為文本/CSV 文件的最輕量、無依賴方式。本文系統講解 SPOOL 核心參數、CSV 格式化最佳配置、大數據量導出效能優化、Windows/Linux 自動化腳本模板。
SPOOL 核心命令與參數
-- 基礎語法
SPOOL [file_name[.ext]] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT
-- 關鍵會話設置 (必須在 SPOOL 前設置)
SET NEWPAGE 0 -- 無分頁換頁符
SET LINESIZE 32767 -- 最大行寬 (防止換行截斷)
SET PAGESIZE 0 -- 抑制標題重複、分頁
SET HEADING OFF -- 不輸出列標題 (或 ON 輸出一次)
SET FEEDBACK OFF -- 不顯示 "N rows selected"
SET VERIFY OFF -- 不顯示變量替換前後對比
SET TRIMSPOOL ON -- 移除行尾空格 (關鍵!否則定寬列尾部大量空格)
SET TAB OFF -- 不使用 TAB 分隔 (配合 COLSEP)
SET COLSEP ',' -- 列分隔符 (CSV 用逗號,TSV 用 TAB)
SET TERMOUT OFF -- 關閉終端輸出 (大幅提速)
SET ECHO OFF -- 不回顯命令
SET TIMING OFF -- 不顯示計時
SET TRIMOUT ON -- 移除輸出行尾空格
SET WRAP OFF -- 不換行 (配合大 LINESIZE)
標準 CSV 導出腳本模板
-- export_employees_csv.sql
-- 使用方式: sqlplus -s user/pass@db @export_employees_csv.sql
SET NEWPAGE 0
SET LINESIZE 32767
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET TAB OFF
SET COLSEP ','
SET TERMOUT OFF
SET ECHO OFF
SET TIMING OFF
SET TRIMOUT ON
SET WRAP OFF
SET NUMFORMAT 999999999999999.99 -- 數值格式,避免科學記數法
-- 動態生成文件名 (含日期時間)
COLUMN spool_file NEW_VALUE spool_file
SELECT 'employees_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') || '.csv' AS spool_file FROM dual;
SPOOL &spool_file
-- 可選:輸出列標題 (手動構造)
-- SELECT 'EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,SALARY' FROM dual;
SELECT
employee_id,
first_name,
last_name,
email,
TO_CHAR(hire_date, 'YYYY-MM-DD') hire_date,
salary
FROM employees
WHERE department_id = 80
ORDER BY employee_id;
SPOOL OFF
EXIT
大數據量導出效能優化
| 優化點 | 設置/做法 | 效果 |
| 關閉終端輸出 | SET TERMOUT OFF | 消除網絡/終端渲染瓶頸,提速 10-50x |
| Array Fetch Size | SET ARRAYSIZE 5000 (默認 15) | 減少網絡往返,每次獲取 5000 行 |
| LOB 處理 | SET LONG 2000000000 SET LONGCHUNKSIZE 32767 | 完整導出 CLOB/BLOB,避免截斷 |
| 並行導出 | 分區表按分區並行 SPOOL 多文件,最後合併 | 利用多 CPU 核心,線性加速 |
| 直接路徑 | 不可直接用於 SPOOL,改用 UTL_FILE 或 DBMS_CLOUD | 繞過 SQL*Plus 格式化層,原始塊輸出 |
自動化部署腳本
Windows 批處理
@echo off
SET ORACLE_SID=ORCL
SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8
sqlplus -s user/pass@ORCL @export_employees_csv.sql
if %errorlevel% neq 0 (
echo Export failed at %date% %time% >> export_error.log
exit /b 1
)
echo Export completed: employees_%date:~-4,4%%date:~-10,2%%date:~-7,2%_*.csv
Linux Shell + Cron 定時任務
#!/bin/bash
# /opt/scripts/export_daily.sh
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=ORCL
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=$ORACLE_HOME/bin:$PATH
LOGFILE="/var/log/oracle_export_$(date +%Y%m%d).log"
SQLSCRIPT="/opt/scripts/export_employees_csv.sql"
sqlplus -s /nolog <> $LOGFILE 2>&1
CONNECT user/pass@ORCL
@$SQLSCRIPT
EXIT
EOF
if [ $? -eq 0 ]; then
echo "$(date) Export SUCCESS" >> $LOGFILE
# 可選:壓縮、傳輸至 S3/HDFS、發送通知
else
echo "$(date) Export FAILED" >> $LOGFILE
mailx -s "Oracle Export Failed" dba@company.com < $LOGFILE
fi
特殊場景處理
- 含換行符的文本欄位:使用
REPLACE(col, CHR(10), ' ') 或 REPLACE(col, CHR(13)||CHR(10), ' ') 替換換行,或改用 JSON/XML 導出。
- 超長行 (>32KB):SQL*Plus
LINESIZE 最大 32767,超長需用 UTL_FILE.PUT_LINE 分塊寫入或 Python/cx_Oracle 導出。
- 編碼問題:確保
NLS_LANG=.AL32UTF8,文件編碼為 UTF-8,Excel 打開需加 BOM 或用 Power Query 導入指定編碼。
- 日期/時間格式統一:
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; 統一輸出格式。
替代方案對比
| 工具/方法 | 優點 | 缺點 | 適用場景 |
| SQL*Plus SPOOL | 零依賴、隨裝隨用、腳本化簡單 | 格式化開銷大、無並行、LOB 受限 | 中小表 (<100萬行)、臨時導出、自動化腳本 |
| SQLcl (現代 CLI) | 支援 JSON/CSV/TSV 格式化、更快、更好編碼 | 需額外安裝 | 常規導出首選 |
| Python cx_Oracle + csv | 完全可控、流式寫入、並行、類型轉換靈活 | 需部署 Python 環境 | 大數據量、ETL 流程、雲上傳 |
| Data Pump (EXPDP) | 極快、並行、含元數據、跨版本 | 二進制格式、需 IMPDP 還原、非人類可讀 | 備份/遷移/同步、非文本交換 |
| Oracle APEX / SQL Developer | GUI 導出、多格式支援 | 手動操作、難自動化 | 一次性導出、業務用戶自助 |
copyright ©
萬盛學電腦網 all rights reserved