oracle数据导出dat文件和ctl文件（使用shell脚本）

君峰相应

已于 2022-03-24 16:14:31 修改

阅读量6.1k

点赞数 4

文章标签：数据库开发 linux

于 2022-03-24 15:48:43 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_45861900/article/details/123711781

版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

前言
一、单表导出
二、多表导出
总结

前言

步骤一是单表导出，可直接使用，输入样式

sh unload.sh 表名 数据源

如果是多表导出，继续看步骤二

提示：如果感觉dat导出方式复杂，请使用exp导出dmp，文档链接: [link](https://blog.youkuaiyun.com/weixin_45861900/article/details/123710533).

一、单表导出

1.编写配置文件

可以使用select userenv(‘language’) from dual; 查询数据库编码格式

select userenv('language') from dual;

配置文件commons.cfg

#字符集
CHARSETAMI=AL32UTF8
NLS_LANG=american_america.AL32UTF8
DL="^C"

配置文件ctlModel.cfg模板参数

load data
characterset CHARSETAMI
infile "TABLE_NAME.dat"
append into table TABLE_NAME
fields terminated by '^C'
trailing nullcols

2.shell脚本unload.sh

#字符集
#!/bin/bash
if [ ! -n "$1" ]\
   || [ ! -n "$2" ];then
echo "可执行程序输入不规范，输入样式 sh unload.sh 表名 数据源"
exit 0
fi
#常用的变量声明
. ~/cfg/commons.cfg
export NLS_LANG=$NLS_LANG
#卸数文件地址   $1表名  $2数据源
echo "--->bin--->unload--->TABLENAME="$1"->"
#导出的目标数据文件
DATAFILENAME=~/data/newData/$1".dat"
#导出的目标ctl控制文件，
CTLFILENAME=~/data/newData/$1".ctl"
#表中含有的所有的列临时文件
colNameTmp=~/data/unloadTemp/$1".col.tmp"
#对列临时文件进行加工使其生成最终版含有的列
colName=~/data/unloadTemp/$1".col"
#对列文件加工处理使其输出成导出SQL语句
sqlName=~/data/unloadTemp/$1".sql"
#根据导出SQL语句，生成数据临时文件
dataFileTmp=~/data/unloadTemp/$1".dat.tmp"
#导出表中的列名，含字符长度，生成的临时文件
colNameTmpCtl=~/data/unloadTemp/$1".col.tmp.ctl"
#对临时文件加工处理输出列文件
colNameCtl=~/data/unloadTemp/$1".col.ctl"


today=`date +"%Y%m%d"`

#拼装卸数表字段 listagg拼装时，查询结构有长度限制
##SQL="SELECT LISTAGG(T.COLUMN_NAME,',')WITHIN GROUP(ORDER BY T.COLUMN_ID) FROM USER_TAB_COLS T WHERE T.TABLE_NAME = 'TBN' ORDER BY T.COLUMN_ID;"
#初始化获取表的所有列，按照模板sql查询
SQL="SELECT t.COLUMN_NAME FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME = 'TBN' ORDER BY T.COLUMN_ID;"
#将模板中定义的sql语句TBN替换为当前导出的表名
SQLE=${SQL/"TBN"/$1}
#获取查询表的所有列
expColumn=$(sqlplus -s $2 <<EOF >$colNameTmp
 set heading off
 set feedback off
 $SQLE
exit
EOF
)

echo "--->bin--->unload--->TABLENAME="$1"-->"
#加工本次应导出的列
##去除空行
grep -v '^\s*$' $colNameTmp>$colName
##拼接行尾+逗号
sed -i 's/$/,/' $colName
##替换最后一行
lastLine=$(cat $colName|tail -1|sed 's/.$//')
sed -i '$d' $colName
echo $lastLine >>$colName

#拼装SQL，导出数据
#去除空行
grep -v '^\s*$' $colNameTmp>$sqlName
sed -i "s/^/ translate(/" $sqlName
sed -i "s/$/ ,chr(10)||chr(13),' ') ||'^C'||/" $sqlName
lastLine=$(cat $colName|tail -1|sed 's/$//')
sed -i '$d' $sqlName
echo $lastLine >>$sqlName

#判断是否存在查询条件
# if [ ! -f "09expTabWhereSql/$1" ];then
echo " FROM "$1";">>$sqlName
# else
# #whereSql=$(cat 09expTabWhereSql/$1)
# echo " FROM "$1" ">>$sqlName
# cat 09expTabWhereSql/$1 | while read LINE 
# do
# echo $LINE >>$sqlName
# done
#lastLineSql=$(cat 09expTabWhereSql/$1|tail -1)
#echo $lastLineSql >>$sqlName
#:echo ";" >>$sqlName
#fi
sed -i '1i\SELECT ' $sqlName

echo "--->bin--->unload--->TABLENAME="$1"--->"
#查询数据并导出结果
sqlplus -s $2<<EOF 
set echo off;
set verify off;
set feedback off;
set heading off;
set pagesize 0;
set null '0';
set linesize 20000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
 spool $dataFileTmp
  @$sqlName
 spool off;
exit
EOF
grep -v '^\s*$' $dataFileTmp>$DATAFILENAME

echo "--->bin--->unload--->TABLENAME="$1"---->"
#创建导出数据的控制文件
if [ -f $CTLFILENAME ];then
 rm $CTLFILENAME
fi

#拼装控制文件，使每个文件后添加长度，预防超长
SQL="SELECT DECODE(T.DATA_TYPE,'VARCHAR2',T.COLUMN_NAME||' CHAR('||T.DATA_LENGTH||')','CHAR',T.COLUMN_NAME||' CHAR('||T.DATA_LENGTH||')',T.COLUMN_NAME) FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME = 'TBN' ORDER BY T.COLUMN_ID;"
SQLE=${SQL/"TBN"/$1}
#获取查询表的所有列
expColumnCtl=$(sqlplus -s $2 <<EOF >$colNameTmpCtl
 set heading off
 set feedback off
 $SQLE
exit
EOF
)
echo "--->bin--->unload--->TABLENAME="$1"----->"
#加工本次应导出的列
##去除空行
grep -v '^\s*$' $colNameTmpCtl>$colNameCtl
##拼接行尾+逗号
sed -i 's/$/,/' $colNameCtl
##替换最后一行
lastLine=$(cat $colNameCtl|tail -1|sed 's/.$//')
sed -i '$d' $colNameCtl
echo $lastLine >>$colNameCtl
#打开ctl模板文件，替换模板参数，并将其输出至ctl文件
cat ~/cfg/ctlModel.cfg|while read LINE
do
LINE=${LINE//"CHARSETAMI"/$CHARSETAMI}
LINE=${LINE//"TABLE_NAME"/$1}
echo $LINE >>$CTLFILENAME
done
#将加工完后的含字符长度的列文件，统一输出至ctl文件
echo "("$(echo $(cat $colNameCtl))")" >>$CTLFILENAME

3.执行脚本

sh unload.sh 表名 数据源

二、多表导出

多表导出主要逻辑是通过脚本循环调用上面的unload.sh

1.创建配置文件

配置文件database.cfg

#中间库数据源
DataBaseMid=abc/\"abc\"@127.0.0.1:1521/orcl

配置文件tyExpTableList.cfg

#需要导出的表名，我记得cfg文件在windows系统打开最后一行需要一个空行，不然无法最后一行无法读取
LP_BNFI_INFO_BAL
LP_BUSI_DUE_BAL
LP_CRDT_APP_BAL
LP_INVE_INFO_BAL

2.编写shell脚本newDataExp.sh

#!/bin/bash

PATH=/usr/java/jdk1.8.0_181/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/openssh-8.6p1/bin:/usr/local/sbin:/home/zyb/bin:/home/zyb/bin:/u01/app/oracle/product/11.2.0/client_1/patch:/u01/app/oracle/product/11.2.0/client_1/bin
export PATH

. ~/cfg/database.cfg
#判断有没有参数输入，没有就采取中间库数据源，有参数则输入用户名及密码
newPath=~/data/newData

if [ ! -n "$1" ];then
 DATABASE=$DataBaseMid
else
 echo "请输入用户名"
 read name

 echo "请输入密码"
 stty -echo
 read password
 stty echo

 DATABASE=$name/$password$DATABASE_SRC
fi
#logFile文件，输出当前执行日志
logFile=~/logs/unloadlog
#数据表导出列表
TL=~/cfg/tyExpTableList.cfg
#循环读取导出列表
cat $TL | while read LINE
do
#暂时屏蔽并发
{
 startTime=`date +%F |sed 's/-//g' ``date +%T | sed 's/://g'`
 echo "--->data--->newDataExp.sh--->TABLENAME="$LINE"---BEGIN---TIME="$startTime >>$logFile/unloadlog.log
 sh unload.sh $LINE $DATABASE
 endTime=`date +%F |sed 's/-//g' ``date +%T | sed 's/://g'`
 echo "--->data--->newDataExp.sh--->TABLENAME="$LINE"---END---DATE="$endTime >>$logFile/unloadlog.log
}&
done

sleep 5

M=`whoami`
echo -e "***中间库卸数完成"