在shell下利用oracle sqlplus中的spool命令导出数据生成文本文件

最新推荐文章于 2021-04-14 02:34:03 发布

weixin_33694172

最新推荐文章于 2021-04-14 02:34:03 发布

阅读量1.1k

点赞数

文章标签：数据库 shell 操作系统

原文链接：https://my.oschina.net/u/234661/blog/811990

版权

2019独角兽企业重金招聘Python工程师标准>>>

使用说明：
本程序的原来是利用SQL Plus的spool命令将从数据导出为文本文件。
程序有三种模式：默认模式，定制模式，并行模式

默认模式：
举例：

sh exp_spool_table.sh -connect=test/123@test

    说明：程序默认导出到当前路径下的data目录下，如果没有此目录程序会自动创建。
       文件名类似 20161204-0.data，若文件已经存在，程序会报错推出，需手动删除后再运行。
       默认的导出sql为当前目录的exp_table.sql.
       -connect参数为sqlplus连接数据库信息，格式为：用户名/密码@TNS名或连接串。
       例如： -connect=test/123@test   若采用TNS名的方式，需要在TNS_ADMIN(环境变量)目录下的tnsnames.ora中配置对应的tns信息
           -connect=test/123@192.168.1.101:1521/orcl

定制模式：
举例：

sh exp_spool_table.sh -connect=test/123@test -sql=/path/to/your.sql

说明：你可以用-sql参数指定导出的sql文件
导出sql举例,导出的子段以逗号分割，日期格式或者包含换行的字段需要进行转换。

select ID||','||to_char('yyyymmddhh24miss',date)||','||replace(text,chr(10),'') from TABLE_NAME;

导出的格式为 10001,20161204191800,备注说明

并行模式：
举例：

sh exp_spool_table.sh -connect=test/123@test -sql=my_sql_1.sql -parallel=true -id=table1
sh exp_spool_table.sh -connect=test/123@test -sql=my_sql_2.sql -parallel=true -id=table2

    说明：对于大量的数据导出的场景，程序提供了并行模式，可以启动多个进程同时进行导出。
       通过-parallel=true来启动并行模式，同时，并行模式必须指定-sql和-id参数。
       -id参数用来区分导出的文件。文件名以"日期-ID.data"的方式命名，
       已启动2个进程为例：
       -id=table1的进程导出的数据文件为 20161204-table1.data，同理，-id=table2的数据文件为 20161204-table2.data
   注意：若多个进程同时导出同一个表的数据，需要在sql文件中通过取模的方式来防止重复导出数据。
       my_sql_1.sql中的内容：

select ID||','||to_char('yyyymmddhh24miss',date)||','||replace(text,chr(10),'') 
from TABLE_NAME
where (ID mod 2) = 0;

my_sql_2.sql中的内容：

select ID||','||to_char('yyyymmddhh24miss',date)||','||replace(text,chr(10),'') 
from TABLE_NAME
where (ID mod 2) = 1;

以下为shell脚本的源代码：

#!/bin/bash

usages="Usages: you can use this shell in three mode listed below: \n
		#easy ,default using exp_table.sql \n
		sh exp_spool_table.sh -connect=test/123@test \n
		sh exp_spool_table.sh -connect=test/123@192.168.1.1:1521/orcl \n
\n
		#customed your export sql \n
		sh exp_spool_table.sh -connect=test/123@test -sql=/path/to/your.sql \n
\n
		#parallel \n
		sh exp_spool_table.sh -connect=test/123@test -sql=my_table_1.sql -parallel=true -id=1 \n
		sh exp_spool_table.sh -connect=test/123@test -sql=my_table_2.sql -parallel=true -id=2"

if [ $# -eq 0 ] ; then
	echo -e $usages
	exit 1
fi

#unique id for parallism ,default:0
ID=0

#export data to this directory,you can change it to your own target directory
export_dir=./data

#default sql file,you can change it by passing the first argument to this process
#example :sh exp_spool_table.sh sql=my_table.sql
export_sql=exp_table.sql

#connection info using by sqlplus
#example:
#	user/passwd@tnsname
#	user/passwd@192.168.1.1:1521/orcl
CONNECTION=""

PARALLEL="false"

#parse arguments
ARGS=`getopt -o "" -al connect:,sql:,parallel:,id: -- "$@"`

if [ $? -ne 0 ] ;then
	echo 'invalid arguments'
	echo -e $usages
	exit 1;
fi 

ARG_NUM=$#
eval set -- "$ARGS" 
while true;
do
	case "$1" in
		 --connect) CONNECTION=$2; shift 2;;
		 --sql) export_sql=$2;  shift 2;;
		 --parallel) PARALLEL=$2;  shift 2;;
		 --id) ID=$2;  shift 2;;
		 --) break ;;
		 *) echo $1,$2; break ;;
	esac
done

if [ $PARALLEL = "true" ] && [ $ARG_NUM -ne 4 ];then
	echo "when you using parallel mode ,you must set all arguments explicit ,do not use default value"
	echo "example:
		sh exp_spool_table.sh -connect=test/123@test -sql=my_table_1.sql -parallel=true -id=1
		sh exp_spool_table.sh -connect=test/123@test -sql=my_table_2.sql -parallel=true -id=2"
	exit 1
fi

if [ ! -f $export_sql ] ; then
	echo "sql file '$export_sql' doesn't exists "
	exit 1
fi

#export data into the file named like '20161225-pid.data'
export_file=`date '+%Y%m%d'`-$ID.data

echo "current dir is '`pwd`' ,running shell '$0' ,pid '$$'"
echo "export data to dir '$export_dir' , file name is '$export_file'"

#if the directory exists , remove it automaticly 
if [ ! -d $export_dir ] ;then
	echo -n "$export_dir is not exists "
	mkdir -p $export_dir
	if [ $? -eq 0 ]; then
		echo ",create it successfully "
	else
		echo "failed to create it ,you can create it manually ,then retry."
		echo "process exits with failure"
		exit 1
	fi
fi

#if the file exists ,remove it automaticly
if [ -f $export_dir/$export_file ] ;then
	echo "failed , $export_dir/$export_file is exists ,please remove it and try again"
	exit 1
	
fi

echo -e "\n===============spooling data using SQLPLUS =========================="
sqlplus -S $CONNECTION<<EOF
set trimspool on
set linesize 2000
set pagesize 1000
set newpage none
set heading off
set term off
set feedback off;
spool $export_dir/$export_file
@"$export_sql"
spool off
quit
EOF
if [ $? -ne 0 ] ; then
	echo "export data failed"
	exit 1
fi
echo `cat $export_dir/$export_file | wc -l` records exported sucessfullly

转载于:https://my.oschina.net/u/234661/blog/811990