Oracle卸数脚本模板：GBK转UTF8与批次号处理实战

简介这份资源是一套面向数据仓库与ETL开发人员的Oracle数据卸载Shell脚本模板适合需要将库内数据按批次导出为文本文件并完成后续传输的工程师使用。包内共4个文件以sh脚本、txt模板与config配置文件为主压缩包仅4KB轻量易部署。使用者只需在SQL模板中填写卸载语句、在文件名配置中指定对应输出文件即可灵活控制卸数逻辑。脚本已集成数据卸载、GBK转UTF8编码转换、批次号获取、尾行追加行数、FTP上传等常用环节并附有将大文件切割为多份的注释语句便于按需启用。配置时需留意环境信息文件中的参数设置。目前已有868人学习下载可作为搭建标准化卸数流程的参考模板帮助读者快速理解批次管理与编码处理思路减少重复开发成本。1. 从一次凌晨三点的卸数事故说起这套 shell 脚本到底解决什么问题凌晨三点被电话叫醒原因是 Oracle 卸数脚本跑出来的文件里金额字段全是乱码下游对账系统直接卡死。排查到天亮才发现数据库字符集是 GBK而下游要求 UTF8中间少了一步编码转换。这种事故在数据卸载场景里太常见了——SQL 写对了文件也生成了但编码、批次号、行数校验、大文件切割这些边角料环节任何一个出问题都能让整条链路翻车。这套 shell 脚本卸载数据模板核心思路是把 Oracle 卸数这件事拆成可配置的流水线你只需要在/etl/sql/sql_mb.txt里写 SQL 语句在/etl/sql/filename.txt里写对应的输出文件名脚本就会自动完成数据卸载、GBK 转 UTF8、批次号获取、尾行加行数、FTP 上传这一整套动作。它还预留了文件切割的注释语句大文件场景直接取消注释就能用。适合谁用做 Oracle ETL 数据抽取的工程师、需要定期把库表数据卸成文本文件给下游系统的运维人员以及正在搭建批量卸数框架的开发者。如果你手头有一堆把这张表导成文件的需求又不想每次都手写 spool 脚本这套模板能省掉大量重复劳动。下面我从配置结构开始一步步拆开讲怎么用、参数怎么改、坑在哪。2. 拆解脚本目录结构与配置逻辑sql_mb.txt 和 filename.txt 怎么配合拿到这个压缩包第一件事不是急着跑脚本而是先搞清楚目录结构和配置文件的对应关系。这套模板的设计哲学是配置与逻辑分离——shell 脚本负责流程控制SQL 和文件名放在独立配置文件里改需求不用动脚本本身。2.1 目录布局与各文件职责解压spool_data.zip后典型的目录结构是这样的/etl/ ├── shell/ │ ├── config/ # 环境信息配置 │ │ └── env.conf # 数据库连接、路径等环境变量 │ └── poolfile.sh # 主卸数脚本 ├── sql/ │ ├── sql_mb.txt # SQL 语句模板 │ └── filename.txt # 输出文件名列表 ├── log/ # 日志目录 ├── data/ # 数据输出目录 └── outdata/ # 卸数完成后的成品目录poolfile.sh是主控脚本它按行读取sql_mb.txt和filename.txt逐条执行卸数。config/env.conf里放的是数据库连接串、字符集、FTP 地址这类环境相关的东西换一套环境只改这个文件就行。log/记录每次执行的详细日志data/是中间产物outdata/是最终交付目录。提示不同版本的模板目录名可能略有差异但shell、sql、log、data、outdata这几个核心目录基本固定。拿到包先tree一下看清楚再动手。2.2 sql_mb.txt 与 filename.txt 的配对规则这两个文件的配合方式是整套模板的核心。sql_mb.txt里一行写一条 SQLfilename.txt里一行写一个文件名脚本按行号一一对应。比如sql_mb.txt内容SELECT cust_id, cust_name, balance FROM customer WHERE status A SELECT order_id, cust_id, amount, create_time FROM orders WHERE create_time TRUNC(SYSDATE)filename.txt内容customer_active orders_today脚本执行时第一条 SQL 的结果输出到customer_active.txt第二条输出到orders_today.txt。这里有个血泪经验两个文件的行数必须严格一致多一行少一行都会导致错位。我一般会在脚本里加一个行数校验不相等直接报错退出。# 行数一致性校验 sql_lines$(wc -l /etl/sql/sql_mb.txt) file_lines$(wc -l /etl/sql/filename.txt) if [ $sql_lines -ne $file_lines ]; then echo ERROR: sql_mb.txt(${sql_lines}) 与 filename.txt(${file_lines}) 行数不一致 /etl/log/error.log exit 1 fi这段校验逻辑很朴素但极其管用。wc -l统计行数-ne判断不等不等就写错误日志并退出。参数说明/etl/log/error.log是错误日志路径按实际环境改。别小看这几行它能拦住 80% 的配置失误。2.3 环境信息配置config 文件里到底该填什么config/env.conf是环境相关配置的集中地常见内容包括数据库连接信息、字符集设置、FTP 参数、路径变量。典型内容如下# 数据库连接配置 DB_USERetl_user DB_PASSetl_pass_2024 DB_SIDORCL DB_HOST192.168.1.100 # 字符集配置 SOURCE_CHARSETGBK TARGET_CHARSETUTF8 # FTP 配置 FTP_HOST192.168.1.200 FTP_USERftp_etl FTP_PASSftp_pass FTP_DIR/data/upload # 路径配置 SQL_DIR/etl/sql DATA_DIR/etl/data OUT_DIR/etl/outdata LOG_DIR/etl/log这里每个变量都有明确用途。DB_SID是 Oracle 实例标识SOURCE_CHARSET和TARGET_CHARSET控制编码转换方向FTP_*系列是上传目标信息。注意DB_PASS和FTP_PASS明文写在配置文件里有安全风险生产环境建议用 Oracle Wallet 或至少把文件权限设成 600。chmod 600 /etl/shell/config/env.conf这条命令把配置文件权限收紧只有属主能读写。别嫌麻烦我见过太多因为配置文件权限过宽导致密码泄露的案例。3. 核心流程逐段拆从 spool 卸数到编码转换的完整链路配置搞清楚之后接下来把poolfile.sh的核心流程拆开看。这套脚本的执行链路是读取配置 → 逐条执行 SQL 卸数 → 编码转换 → 获取批次号 → 尾行加行数 → FTP 上传。每一段都有值得说道的细节。3.1 spool 卸数SQL 怎么变成文本文件Oracle 卸数最常用的方式就是 SQL*Plus 的 spool 命令。脚本里对每条 SQL 的处理逻辑大致如下# 逐条执行卸数 while read -r sql_line; do # 读取对应的文件名 read -r file_name 3 # 构造 spool 脚本 spool_script${DATA_DIR}/${file_name}.sql cat $spool_script EOF SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF SET TRIMSPOOL ON SET LINESIZE 32767 SET TERMOUT OFF SPOOL ${DATA_DIR}/${file_name}.txt ${sql_line}; SPOOL OFF EXIT EOF # 执行 spool sqlplus -s ${DB_USER}/${DB_PASS}${DB_HOST}/${DB_SID} $spool_script ${LOG_DIR}/${file_name}.log 21 if [ $? -ne 0 ]; then echo ERROR: ${file_name} 卸数失败 ${LOG_DIR}/error.log continue fi done $SQL_DIR/sql_mb.txt 3 $SQL_DIR/filename.txt这段代码有几个关键点。SET PAGESIZE 0去掉分页SET HEADING OFF去掉列标题SET TRIMSPOOL ON去掉行尾空格SET LINESIZE 32767把行宽拉到最大防止折行。sqlplus -s的-s参数是静默模式不输出 SQL*Plus 的版本信息。$?判断上一条命令的退出码非零就记错误日志并跳过当前文件继续下一个。参数说明DB_USER/DB_PASSDB_HOST/DB_SID是 Oracle 标准连接串格式如果用的是服务名而非 SID把/换成/service_name。是追加写入日志21把标准错误也重定向到日志文件。注意SET LINESIZE最大值取决于 Oracle 版本10g 是 32767更早版本可能只有 32767 的一半。如果发现输出被截断先检查这个参数。3.2 GBK 转 UTF8编码转换的两种实现方式编码转换是这套脚本的刚需功能。Oracle 数据库字符集如果是 GBKspool 出来的文件默认就是 GBK 编码而下游系统通常要求 UTF8。脚本里一般用iconv做转换# 编码转换GBK - UTF8 convert_encoding() { local src_file$1 local dst_file$2 iconv -f ${SOURCE_CHARSET} -t ${TARGET_CHARSET} $src_file -o $dst_file if [ $? -ne 0 ]; then echo ERROR: ${src_file} 编码转换失败 ${LOG_DIR}/error.log return 1 fi return 0 }iconv -f指定源编码-t指定目标编码-o指定输出文件。SOURCE_CHARSET和TARGET_CHARSET从env.conf读取。这里有个玄学问题如果源文件里混了非法字符iconv会直接报错退出。解决办法是加-c参数忽略无法转换的字符iconv -f GBK -t UTF8 -c $src_file -o $dst_file但-c是双刃剑它会静默丢弃无法转换的字符可能导致数据丢失。我的建议是先不加-c跑一遍看日志里有没有转换错误有的话再决定是清洗源数据还是加-c兜底。3.3 批次号获取与尾行加行数批次号是卸数场景里区分不同批次数据的标识。脚本里获取批次号的方式通常有两种从数据库序列取或者用时间戳生成。# 方式一从 Oracle 序列获取批次号 BATCH_NO$(sqlplus -s ${DB_USER}/${DB_PASS}${DB_HOST}/${DB_SID} EOF SET HEADING OFF SET FEEDBACK OFF SELECT seq_batch_no.NEXTVAL FROM DUAL; EXIT EOF ) # 方式二用时间戳生成 BATCH_NO$(date %Y%m%d%H%M%S)方式一适合需要持久化批次号的场景序列值不会重复。方式二简单直接但高并发下可能撞号。选哪种取决于下游系统对批次号的要求。尾行加行数是数据校验的常见需求——在文件末尾追加一行记录数方便下游核对。实现方式# 尾行加行数 add_line_count() { local file$1 local line_count$(wc -l $file) echo TOTAL_COUNT:${line_count} $file }wc -l统计行数echo追加到文件末尾。注意如果文件最后一行没有换行符wc -l会少算一行。稳妥做法是先用sed -i $a\补一个换行符再统计。3.4 FTP 上传与文件切割的预留逻辑FTP 上传部分用ftp命令的 here document 实现# FTP 上传 upload_file() { local local_file$1 local remote_name$(basename $local_file) ftp -n $FTP_HOST EOF user ${FTP_USER} ${FTP_PASS} binary cd ${FTP_DIR} put ${local_file} ${remote_name} bye EOF }-n参数禁止自动登录user命令手动传用户名密码binary设为二进制模式防止文件被篡改put上传文件。如果 FTP 服务器支持被动模式可能还需要加passive命令。文件切割的语句在脚本里是注释掉的针对大文件场景# 大文件切割默认注释需要时取消注释 # split -l 100000 ${DATA_DIR}/${file_name}.txt ${DATA_DIR}/${file_name}_part_split -l 100000表示每 10 万行切一个文件输出文件前缀是文件名_part_。取消注释前先确认下游系统能处理多个分片文件否则切了反而添乱。4. 避坑与排查卸数脚本最常见的五类翻车现场这套脚本用熟了确实省事但新手上来直接跑大概率会踩几个坑。下面这五类问题是我和同事实际遇到过的按现象 → 原因 → 解决整理出来。4.1 输出文件为空或只有一行现象脚本执行完data/目录下生成了.txt文件但打开一看是空的或者只有一行空白。原因最常见的是 SQL 语句末尾带了分号。spool 脚本里 SQL 后面跟分号SQLPlus 会把分号当成语句结束符但 spool 的输出控制可能因此异常。另一个原因是SET TERMOUT OFF没设SQLPlus 把结果输出到终端而非文件。解决检查sql_mb.txt里的 SQL 是否带了不必要的分号去掉它。确认 spool 脚本里有SET TERMOUT OFF。如果还不行手动执行一次 spool 脚本看终端输出。4.2 编码转换后中文变问号现象GBK 转 UTF8 之后中文字段全变成???或乱码。原因iconv的源编码设错了。比如数据库实际是ZHS16GBK但env.conf里写的是GB2312两者不完全兼容。或者数据库字符集是AL32UTF8根本不需要转换强行转反而出错。解决先用SELECT * FROM nls_database_parameters WHERE parameter NLS_CHARACTERSET;确认数据库真实字符集。如果已经是 UTF8把SOURCE_CHARSET和TARGET_CHARSET都设成UTF8相当于不做转换。如果是 GBK确保SOURCE_CHARSET写GBK而非GB2312。4.3 批次号重复导致数据覆盖现象两次卸数生成的批次号一样下游系统把两批数据当成同一批处理。原因用时间戳生成批次号时如果两次执行间隔不到一秒date %Y%m%d%H%M%S会生成相同的值。解决改用数据库序列或者在时间戳后面加进程号$$BATCH_NO$(date %Y%m%d%H%M%S)_$$$$是当前 shell 的进程 ID几乎不会重复。更稳妥的方案是用 Oracle 序列彻底避免撞号。4.4 FTP 上传失败但脚本不报错现象脚本执行完显示成功但 FTP 服务器上找不到文件。原因ftp命令的退出码不一定反映实际上传结果。如果put失败ftp可能仍然返回 0。另外binary模式没设的话文本文件可能被 FTP 服务器做换行转换导致文件损坏。解决在 FTP here document 里加ls命令确认文件存在或者上传后用ftp的size命令比对文件大小。更可靠的做法是改用lftp或curl做上传它们的退出码更准确。4.5 大文件切割后下游系统不认现象取消注释split语句后生成了多个分片文件但下游系统只读取了第一个分片。原因下游系统的文件读取逻辑可能只认固定文件名不认_part_后缀的分片。或者分片文件的行数不一致导致校验失败。解决切割前先和下游确认分片命名规则和读取方式。如果下游不支持分片改用其他方案比如压缩后传输再解压。split的-l参数按行数切割如果按大小切割用-b参数比如split -b 100M。5. 进阶技巧让卸数脚本更稳的几个习惯脚本能跑通只是第一步生产环境里真正考验人的是稳定性。下面这几个习惯是我踩了无数坑之后养成的分享出来供参考。5.1 用日志分级替代 echo 调试新手调试脚本喜欢到处echo跑完再删。更好的做法是从一开始就做日志分级log_info() { echo [$(date %Y-%m-%d %H:%M:%S)] [INFO] $* ${LOG_DIR}/poolfile.log } log_error() { echo [$(date %Y-%m-%d %H:%M:%S)] [ERROR] $* ${LOG_DIR}/error.log }log_info写常规日志log_error写错误日志。排查问题时先看error.log没有错误再看poolfile.log的执行轨迹。日志里带时间戳能快速定位是哪一步卡住了。5.2 用 trap 捕获异常退出脚本跑到一半被中断可能留下不完整的输出文件。用trap捕获信号在退出前做清理cleanup() { log_info 脚本被中断清理临时文件 rm -f ${DATA_DIR}/*.sql exit 1 } trap cleanup SIGINT SIGTERMSIGINT是 CtrlC 触发的信号SIGTERM是 kill 命令的默认信号。捕获到这两个信号就执行cleanup函数删掉临时文件再退出。这样不会留下半成品文件误导下游。5.3 用 md5 校验确保文件完整性FTP 上传最怕文件损坏。上传前后各算一次 md5比对一致才算成功# 上传前算 md5 local_md5$(md5sum $local_file | awk {print $1}) # 上传后从 FTP 服务器取 md5需要服务器支持 remote_md5$(ftp -n $FTP_HOST EOF | grep -oE [a-f0-9]{32} user ${FTP_USER} ${FTP_PASS} binary md5sum ${FTP_DIR}/${remote_name} bye EOF ) if [ $local_md5 ! $remote_md5 ]; then log_error 文件 ${remote_name} md5 校验失败 return 1 fimd5sum算本地文件哈希awk {print $1}取第一列。FTP 服务器端算 md5 需要服务器支持md5sum命令不是所有 FTP 都支持。如果不支持退而求其次比对文件大小。5.4 参数速查表参数作用常见值注意事项SET PAGESIZE每页行数00 表示不分页SET LINESIZE行宽32767超长会被截断SET TRIMSPOOL去行尾空格ON建议开启SET TERMOUT终端输出OFF卸数时必须 OFFiconv -f源编码GBK需与数据库一致iconv -t目标编码UTF8按下游要求设split -l按行切割100000按需调整split -b按大小切割100M与 -l 二选一这张表建议打印出来贴在工位上改参数的时候对照着看比翻文档快。5.5 一个具体技巧用 expect 替代明文密码env.conf里明文存密码始终不踏实。如果环境里装了expect可以用它做交互式登录密码从环境变量读取#!/usr/bin/expect -f set timeout 30 set db_pass $env(DB_PASS) spawn sqlplus -s ${DB_USER}${DB_SID} expect Enter password: send ${db_pass}\r expect SQL send SPOOL ${DATA_DIR}/${file_name}.txt\r send ${sql_line};\r send SPOOL OFF\r send EXIT\r expect eofspawn启动 sqlplusexpect等待密码提示send发送密码。密码从环境变量DB_PASS读取不落盘。这个方案比明文配置安全但expect的脚本调试起来比较麻烦适合对安全要求高的场景。从那以后我每次部署卸数脚本都强制走一遍行数校验 → 编码确认 → 批次号唯一性检查 → FTP 连通性测试这四步宁可多花十分钟也不想再被凌晨三点的电话叫醒。希望帮到你。本文还有配套的精品资源点击获取