简介这是一份面向数据仓库与ETL开发人员的Oracle数据卸载Shell脚本模板适合需要将库内数据按批次导出为文本并完成后续处理的工程师使用。脚本通过配置SQL模板文件与文件名映射文件即可灵活指定卸载内容无需改动核心逻辑降低了重复开发成本。压缩包共4个文件包含2个txt配置模板、1个sh主脚本和1个config环境配置文件整体仅4KB轻量易部署。功能覆盖数据卸载、GBK转UTF8编码转换、批次号获取、尾行追加行数、FTP上传并附有文件切割语句注释供大文件场景启用。使用前需注意配置config中的环境信息。目前已有868人学习下载读者可借此快速搭建可复用的卸数流程掌握编码转换与批次管理的实现思路并参考文件切割与上传环节的写法减少从零编写脚本的试错成本。1. 一套 Oracle 卸数脚本为什么老 DBA 还在用 shell 手搓上周帮一个做数仓的朋友看他们的卸数流程凌晨两点跑批失败日志里全是乱码。翻到根目录一看一个叫poolfile.sh的脚本孤零零躺在那儿旁边是config、etl、sql三个目录。这套结构我太熟了——典型的 Oracle 卸数模板用 shell 把 SQL 查询结果 spool 成文本再做编码转换、批次号拼接、行数统计、FTP 上传。听起来土但银行、保险、制造业的数据交换场景里这套东西跑了十几年还在跑。它解决的核心问题很具体业务系统需要把 Oracle 里的数据以纯文本形式交给下游下游可能是老式主机、可能是文件接口平台不跟你讲 JDBC、不跟你讲 API就要一个定长或分隔符文本。这时候 shell sqlplus 的 spool 是最短路径。适合谁适合手头有 Oracle 实例、需要定时批量卸数、又不想为这点事上一套 ETL 工具的人。脚本本身不复杂但配置项和编码坑能把人折腾到怀疑人生下面拆开讲。2. 拆开spool_data.zip目录结构与配置项到底怎么对应拿到压缩包先别急着跑把目录树看清楚。这套模板的目录设计是有讲究的每个目录承担不同职责混在一起改迟早出事。2.1 四个核心目录的职责划分解压后典型结构如下spool_data/ ├── config/ │ ├── etl/ │ │ ├── shell/ │ │ │ └── config # 环境信息数据库连接、路径、FTP │ │ ├── sql/ │ │ │ ├── sql_mb.txt # SQL 模板文件 │ │ │ └── filename.txt # 输出文件名映射 │ │ └── poolfile.sh # 主卸数脚本 │ └── ... ├── data/ # 卸数输出目录 ├── log/ # 运行日志 └── outdata/ # 最终交付目录config/etl/shell/config是环境信息集中地数据库连接串、spool 路径、FTP 地址都从这里读。config/etl/sql/sql_mb.txt放你要执行的 SQL 语句filename.txt放对应的输出文件名。poolfile.sh是主控脚本按行读取 SQL 和文件名逐个执行。data是中间落盘目录outdata是加工完准备上传的目录log记录每次跑批的详细输出。提示config文件里如果有明文密码至少把权限设成 600别用 644 裸奔。2.2sql_mb.txt与filename.txt的配对逻辑这两个文件是成对使用的行号一一对应。sql_mb.txt第 1 行的 SQL 查出来的数据写到filename.txt第 1 行指定的文件里。常见写法-- sql_mb.txt SELECT cust_id || | || cust_name || | || TO_CHAR(open_date,YYYYMMDD) FROM customer WHERE status A SELECT order_id || | || cust_id || | || TO_CHAR(order_amt,FM999999990.00) FROM orders WHERE create_date TRUNC(SYSDATE)-- filename.txt customer_active.txt order_daily.txt注意 SQL 里用||拼分隔符而不是靠 sqlplus 的colsep。原因是colsep对 NULL 值的处理在不同 Oracle 版本里表现不一致有的版本 NULL 直接输出空有的输出空格下游解析会翻车。手动拼||虽然啰嗦但 NULL 会变成空字符串行为可控。日期字段一定用TO_CHAR显式格式化别指望NLS_DATE_FORMAT环境变量那东西在 crontab 里经常不生效。2.3poolfile.sh主流程逐段拆解脚本主体逻辑不复杂但每段都有细节。核心循环大致长这样#!/bin/bash # poolfile.sh - Oracle 卸数主脚本 source ./config/etl/shell/config SQL_FILE./config/etl/sql/sql_mb.txt FN_FILE./config/etl/sql/filename.txt BATCH_NO$(date %Y%m%d%H%M%S) # 批次号不同批次卸数用 line_num0 while read -r sql_line; do line_num$((line_num 1)) out_name$(sed -n ${line_num}p $FN_FILE) [ -z $out_name ] continue # 执行 SQL 并 spool 到 data 目录 sqlplus -s ${DB_USER}/${DB_PASS}${DB_SID} EOF SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF SET TRIMSPOOL ON SET LINESIZE 32767 SPOOL ${DATA_DIR}/${out_name} ${sql_line} SPOOL OFF EXIT EOF # 编码转换 GBK - UTF8 iconv -f GBK -t UTF-8 ${DATA_DIR}/${out_name} ${DATA_DIR}/${out_name}.utf8 # 尾行加行数 row_count$(wc -l ${DATA_DIR}/${out_name}.utf8) echo #TOTAL_ROWS${row_count} ${DATA_DIR}/${out_name}.utf8 mv ${DATA_DIR}/${out_name}.utf8 ${OUTDATA_DIR}/${out_name} done $SQL_FILE逐段说明source config把环境变量加载进来BATCH_NO用时间戳生成批次号后面拼在文件名或日志里区分不同批次。while read逐行读 SQLsed -n取对应行文件名。sqlplus -s静默模式SET PAGESIZE 0去掉分页SET HEADING OFF去掉列名SET TRIMSPOOL ON去掉行尾空格——这个很关键不去掉的话每行后面一堆空格文件体积翻倍。SPOOL指定输出文件执行 SQLSPOOL OFF结束。编码转换用iconv从 GBK 转 UTF-8。为什么会有 GBK因为很多 Oracle 客户端环境NLS_LANG设的是SIMPLIFIED CHINESE_CHINA.ZHS16GBKspool 出来的文件就是 GBK 编码。下游如果要求 UTF-8必须转。wc -l统计行数追加到文件尾部作为尾行。最后mv到outdata目录。注意wc -l统计的是换行符数量如果最后一行没有换行符会少算一行。spool 出来的文件通常每行都有换行但保险起见可以在 SQL 里确保每条记录完整输出。3. 从 spool 到 FTP编码转换、批次号与上传的完整链路上一章把脚本骨架过了一遍这一章把几个关键环节展开编码转换的坑、批次号怎么用、FTP 上传怎么写、大文件切割怎么处理。3.1 GBK 转 UTF8 的时机与iconv参数编码转换的时机很重要。必须在 spool 完成后、上传之前做。如果在 spool 过程中转sqlplus 输出流被拦截容易出乱码。iconv的基本用法iconv -f GBK -t UTF-8 input.txt -o output.txt # 或者用重定向 iconv -f GBK -t UTF-8 input.txt output.txt-f指定源编码-t指定目标编码。常见问题是遇到无法转换的字符iconv默认报错退出。加-c参数可以忽略无法转换的字符iconv -f GBK -t UTF-8 -c input.txt output.txt但-c是双刃剑忽略的字符直接丢掉数据就缺了。我一般先不加-c跑一遍看报错在哪个字符确认是脏数据还是编码判断错了。如果源文件实际是 GB18030 而不是 GBK用-f GB18030能覆盖更多字符。判断源编码可以用file -i命令file -i data/customer_active.txt # 输出类似data/customer_active.txt: text/plain; charsetiso-8859-1file -i不一定准但对中文文本如果显示iso-8859-1或unknown-8bit大概率是 GBK 系。更可靠的办法是拿一个已知编码的样本对比或者用enca工具检测。3.2 批次号生成与多批次卸数的隔离批次号的作用是区分不同时间跑的卸数任务。比如一天跑四次每次生成的文件名里带批次号下游就能知道哪份是最新的。生成方式BATCH_NO$(date %Y%m%d%H%M%S) # 或者带毫秒 BATCH_NO$(date %Y%m%d%H%M%S)_$$$$是当前 shell 的 PID加在后面防止同一秒内多次执行冲突。批次号可以拼在文件名里out_name${out_name%.txt}_${BATCH_NO}.txt也可以写在文件内容的第一行或尾行。我倾向于拼在文件名里下游按文件名排序就能拿到最新批次。如果下游要求文件名固定那就把批次号写在尾行注释里比如#BATCH_NO20250101120000。多批次隔离还有一个问题data目录和outdata目录要不要按批次建子目录如果每天跑很多次建议按批次建子目录mkdir -p ${DATA_DIR}/${BATCH_NO} mkdir -p ${OUTDATA_DIR}/${BATCH_NO}这样每次跑批的输出互不干扰出问题也好回溯。3.3 FTP 上传脚本与失败重试FTP 上传部分模板里通常用ftp命令的 here document 写法ftp -n $FTP_HOST EOF user $FTP_USER $FTP_PASS binary cd $FTP_REMOTE_DIR lcd $OUTDATA_DIR put $out_name bye EOF-n禁止自动登录user手动传用户名密码。binary设二进制模式避免文本模式换行符被转换。cd切远程目录lcd切本地目录put上传单个文件。失败重试可以包一层循环upload_retry() { local file$1 local max_retry3 local count0 while [ $count -lt $max_retry ]; do if ftp -n $FTP_HOST EOF user $FTP_USER $FTP_PASS binary cd $FTP_REMOTE_DIR put $file bye EOF then echo 上传成功: $file return 0 fi count$((count 1)) echo 上传失败第 $count 次重试: $file sleep 5 done echo 上传最终失败: $file return 1 }注意ftp命令的返回值不一定可靠有些版本即使上传失败也返回 0。更稳妥的做法是上传后检查远程文件大小或者用lftp替代lftp的返回值更准确。如果环境里没有lftp那就只能靠日志和人工巡检。3.4 大文件切割split命令的注释与启用模板里有一段被注释掉的切割逻辑针对大文件。启用方式# 大文件切割每 100 万行一个文件 if [ $(wc -l $out_file) -gt 1000000 ]; then split -l 1000000 -d -a 4 $out_file ${out_file}.part_ rm -f $out_file # 切割后的文件逐个上传 for part in ${out_file}.part_*; do upload_retry $part done else upload_retry $out_file fisplit -l 1000000按行数切割-d用数字后缀-a 4后缀长度 4 位。切割后的文件名类似customer_active.txt.part_0000、customer_active.txt.part_0001。下游需要按顺序拼接所以命名要保证排序正确。切割的坑在于如果文件里有跨行的字段比如 CLOB 字段里有换行按行切割会把一条记录切到两个文件里。所以切割前要确认数据里没有内嵌换行。有的话要么在 SQL 里把换行替换掉要么改用按字节切割split -b但按字节切割同样可能切断记录。最稳妥的是在 SQL 层面保证每条记录一行用REPLACE把换行符去掉SELECT REPLACE(REPLACE(content, CHR(10), ), CHR(13), ) FROM ...4. 避坑与排查卸数脚本最常见的五类翻车现场这套脚本跑起来不难难的是出问题时怎么快速定位。下面五类问题是我踩过或见别人踩过的按「现象 → 原因 → 解决」写。4.1 现象spool 文件为空但 SQL 单独执行有数据原因sqlplus连接的环境和手动执行的环境不一致。常见情况是config里的DB_SID或TNS_ADMIN没设对或者NLS_LANG在 crontab 里没继承。另一个可能是 SQL 末尾没有分号sqlplus在 here document 里对分号敏感缺分号 SQL 不执行。解决在脚本里显式 export 环境变量export NLS_LANGSIMPLIFIED CHINESE_CHINA.ZHS16GBK export TNS_ADMIN/path/to/tns export ORACLE_HOME/path/to/oracle/home export PATH$ORACLE_HOME/bin:$PATH然后在sqlplus里加SET ECHO ON和SET VERIFY ON把执行的 SQL 打印到日志确认 SQL 真的传进去了。4.2 现象中文变问号或乱码原因NLS_LANG和iconv的编码不匹配。比如NLS_LANG设的是ZHS16GBKspool 出来是 GBK但iconv用-f UTF-8去转结果全乱。或者NLS_LANG设的是AL32UTF8spool 出来已经是 UTF-8又用iconv -f GBK转一遍同样乱。解决先确认 spool 文件的真实编码。用hexdump -C看中文字节hexdump -C data/customer_active.txt | head -5GBK 的中文是两个字节高位在 0x81-0xFEUTF-8 的中文是三个字节以 0xE 开头。确认后再决定iconv的参数。最稳的办法是统一NLS_LANG为AL32UTF8spool 出来直接是 UTF-8跳过iconv步骤。但有些老 Oracle 数据库字符集是 ZHS16GBK客户端设AL32UTF8会做转换可能丢字符。那就保持NLS_LANG和数据库字符集一致spool 后用iconv转。4.3 现象尾行行数比实际少一行原因wc -l统计换行符如果文件最后一行没有换行符就少算。spool 出来的文件通常每行都有换行但某些情况下最后一行可能没有。解决用awk统计行数它对最后一行没有换行符的情况处理更准确row_count$(awk END{print NR} $out_file)或者统计完后加 1 判断row_count$(wc -l $out_file) if [ -n $(tail -c 1 $out_file) ]; then row_count$((row_count 1)) fitail -c 1取最后一个字节如果不是换行符说明最后一行没换行行数加 1。4.4 现象FTP 上传成功但远程文件大小为 0原因ftp的put命令在文件还没写完时就返回了或者binary模式没设文本模式传输时遇到特殊字符中断。另一个可能是本地文件路径不对put传了个空文件。解决上传前检查本地文件大小if [ ! -s $out_file ]; then echo 文件为空跳过上传: $out_file return 1 fi上传后检查远程文件大小可以用ftp的ls命令ftp -n $FTP_HOST EOF user $FTP_USER $FTP_PASS cd $FTP_REMOTE_DIR ls -l $out_name bye EOF对比本地和远程大小不一致就重传。如果环境支持改用sftp或scp更可靠但很多老环境只开了 FTP。4.5 现象crontab 里跑失败手动执行正常原因crontab 的环境变量和登录 shell 不一样。PATH可能不包含sqlplus、iconv、ftp的路径NLS_LANG、ORACLE_HOME等也没继承。解决在脚本开头显式设置所有环境变量或者在 crontab 里 source 用户的 profile# crontab 写法 0 2 * * * . /home/oracle/.bash_profile /path/to/poolfile.sh /path/to/log/cron.log 21更推荐在脚本内部自己设置不依赖外部 profile。把ORACLE_HOME、PATH、NLS_LANG、TNS_ADMIN都在脚本开头 export 一遍这样不管谁调用、怎么调用环境都一致。5. 进阶把卸数脚本改造成可配置、可监控的批处理框架这套模板本身够用但如果要跑几十个卸数任务手动维护sql_mb.txt和filename.txt就累了。我一般会做几个改造让它更像一个小型批处理框架。5.1 用配置文件驱动多任务把每个卸数任务写成一个独立的配置文件放在config/tasks/目录下# config/tasks/customer_active.conf SQLSELECT cust_id || | || cust_name FROM customer WHERE status A OUTPUTcustomer_active.txt ENCODINGGBK SPLIT_LINES0 FTP_DIR/data/customer主脚本遍历config/tasks/*.conf逐个 source 并执行。这样新增任务不用改主脚本加个配置文件就行。配置文件里可以控制编码、是否切割、FTP 目录等参数灵活性高很多。5.2 日志分级与关键节点打点日志不要只往一个文件里堆按级别分log_info() { echo [INFO] $(date %Y-%m-%d %H:%M:%S) $* $LOG_FILE; } log_error() { echo [ERROR] $(date %Y-%m-%d %H:%M:%S) $* $LOG_FILE; } log_info 开始卸数: $out_name log_error SQL 执行失败: $sql_line关键节点打点SQL 开始、SQL 结束、编码转换开始、编码转换结束、上传开始、上传结束。每个节点记录时间戳跑批慢了能看出卡在哪一步。如果接监控系统可以在日志里输出特定格式让监控 agent 抓取。5.3 失败任务的重跑与断点续传跑批失败后不要整个重跑只重跑失败的任务。用一个状态文件记录每个任务的执行状态STATUS_FILE./log/task_status_${BATCH_NO}.txt # 执行成功后写入 echo ${out_name}:SUCCESS $STATUS_FILE # 执行失败后写入 echo ${out_name}:FAILED $STATUS_FILE重跑时先读状态文件跳过 SUCCESS 的任务if grep -q ^${out_name}:SUCCESS $STATUS_FILE 2/dev/null; then log_info 跳过已完成任务: $out_name continue fi这样即使跑批中途失败重跑时也只处理失败的部分节省时间。状态文件按批次号命名不同批次互不影响。5.4 验证卸数结果的三个检查点卸数完成后怎么确认数据没问题我一般做三个检查检查项方法预期行数一致对比源表 count 和文件行数差值在允许范围内编码正确file -i检查文件编码UTF-8 或 GBK 符合配置尾行完整tail -1看尾行标记包含#TOTAL_ROWS行数对比可以在 SQL 里加一个 count 查询和文件行数比对。编码检查用file -i。尾行检查确认脚本的尾行追加逻辑执行了。三个检查都过基本可以放心上传。提示如果下游对数据质量要求高可以在文件头加一个校验和比如md5sum的值下游收到后校验。从那以后我每次改完卸数脚本都强制走一遍「空跑 → 小批量 → 全量」的流程确认编码、行数、上传都没问题再上生产。这套模板不复杂但细节多希望帮到你。本文还有配套的精品资源点击获取