本文介绍了一种在Oracle数据库中高效导入导出数据的方法，通过编写shell脚本实现数据的批量导出与导入，包括unload.sh用于导出数据到文本文件，load.sh用于将文本文件中的数据导入到数据库。
oracle的文本导入、导出技巧（参照informix的load,unload）
--------------------------------------------------------------------------------
http://www.chinaunix.net 作者:playboy 发表于：2006-03-22 23:27:34
【发表评论】【查看原文】【Oracle讨论区】【关闭】
在使用oracle时，总觉得oracle的导入，导出没有informix的load,unload好用，没办法，只能参照网上朋友的思路和informix的实现，写了几个脚本，希望各位高手指点：
=============================================
工具用法：
unload.sh
Usage: unload.sh userid/passwd[@oraclesid] [tabname]
注释：
如果不加’@oraclesid’，则用当前环境变量ORACLE_SID。
如果不加tabname参数，则处理该用户下的所有表，生成’表名.unl’文件。
如果加tabname参数，则单独处理该表，生成’表名.unl’文件。
load.sh
Usage: load.sh userid/passwd[@oraclesid] [tabname]
注释：
如果不加’@oraclesid’，则用当前环境变量ORACLE_SID。
如果不加tabname参数，则装载该用户下的所有表，必须存在’表名.unl’
文件。
如果加tabname参数，则单独处理该表，必须存在’表名.unl’文件。
=============================================
具体实现：
unload.sh
利用spool的功能，将表中的数据导出到对应的文本中。
load.sh
利用sql*load的功能，将文本中的数据导入到表中。
控制文件是自动生成的，使用者不需要知道格式
执行sqlload的脚本也是自动生成
generate_control_file.sh
生成控制文件
generate_execute_shell.sh
生成执行脚本
=============================================
代码说明
load.sh
=============================================
#!/usr/bin/ksh
################################################################################
#
# Ä&&é: load.sh
#
# ÃèÊ&: &ù¾ÝÒ&&&Ê&¾Ý&&&ò±íÃ&À&×°ÔØ&ÔÓ&&ÄÊ&¾ÝÎļ&
#
# ²ÎÊ& 1 = ÓÃ&§Ã&/ÃÜÂ&[@Ê&À&Ã&]
# ²ÎÊ& 2 = ±íÃ&(&ÉÑ&)
#
# ×÷Õ& Bing He
#
# ÐÞ&ļǼ
# ÈÕÆÚ ÐÞ&ÄÈË ÐÞ&ÄÃèÊ&
#
# 09/23/2003 Bing He &&ʼ±àÐ&
#
################################################################################
lv_temp1="wk.test1"
f_get_tables()
{
rm -f ${lv_temp1}
sqlplus ${userid} <<! >;/dev/null
set colsep $sep;
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool ${lv_temp1};
select table_name from user_tables;
spool off;
exit
!
if [ "$?" -ne 0 ]
then
echo "Error:sqlplus ${userid} error in load for ${userid} !"
echo "please check userid and passwd or oracle_sid."
exit
fi
if [ -f ${lv_temp1} ]
then
lv_tables=`cat ${lv_temp1} |grep -v "^SQL>;" | tr -d ' '`
else
echo "Error:${lv_temp1} file not found!"
exit
fi
rm -f ${lv_temp1}
}
################################################################################
## Ö÷³ÌÐòÈ&&Ú
lv_no=$#
case ${lv_no} in
1 )
userid=$1
f_get_tables;
;;
2 )
userid=$1
lv_tables=$2
;;
* )
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
;;
esac
for lv_table in ${lv_tables}
do
if [ ! -f ${lv_table}.unl ]
then
echo "Error:${lv_table}.unl file not found!"
else
generate_control_file.sh ${userid} ${lv_table}
generate_execute_shell.sh ${userid} ${lv_table}
sh load_${lv_table}.sh
rm -f ${lv_table}.ctl
rm -f load_${lv_table}.sh
fi
done
################################################################################
=============================================
unload.sh
=============================================
#!/usr/bin/ksh
################################################################################
#
# Ä&&é: unload.sh
#
# ÃèÊ&: &ù¾ÝÒ&&&Ê&¾Ý&&&ò±íÃ&À&Ð&ÔØÊ&¾Ý²&Éú³É&ÔÓ&&ÄÊ&¾ÝÎļ&
#
# ²ÎÊ& 1 = ÓÃ&§Ã&/ÃÜÂ&[@Ê&À&Ã&]
# ²ÎÊ& 2 = ±íÃ&(&ÉÑ&)
#
# ×÷Õ& Bing He
#
# ÐÞ&ļǼ
# ÈÕÆÚ ÐÞ&ÄÈË ÐÞ&ÄÃèÊ&
#
# 09/23/2003 Bing He &&ʼ±àÐ&
#
################################################################################
lv_sep='|' # --·Ö&&·&&&&ÉÒÔÐÞ&ijÉ×Ô¼&Ï&Ò&&Ä·Ö&&·&&&È&'|'
lv_temp1="unload.temp1"
f_get_tables()
{
rm -f ${lv_temp1}
sqlplus ${userid} <<! >;/dev/null
set colsep ${lv_sep};
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool ${lv_temp1};
select table_name from user_tables;
spool off;
exit
!
if [ "$?" -ne 0 ] ; then
echo "sqlplus $userid error in get table name <"$?">;!!"
echo "please check userid and passwd or database."
exit
fi
if [ -f ${lv_temp1} ]
then
lv_tables=`cat ${lv_temp1} |grep -v "^SQL>;" | tr -d ' '`
else
echo "Error:${lv_temp1} file not found!"
exit
fi
rm -f ${lv_temp1}
}
################################################################
## Ö÷³ÌÐòÈ&&Ú
lv_no=$#
case ${lv_no} in
1 )
userid=$1
f_get_tables;
;;
2 )
userid=$1
lv_tables=$2
;;
* )
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
;;
esac
################################################################
################################################################
## Ö&ÐÐÏÂÔزÙ×÷
for table in ${lv_tables}
do
rm -f lv_$table.txt
sqlplus ${userid} <<! >;/dev/null
set colsep ${lv_sep};
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool lv_$table.txt;
select * from $table;
spool off;
!
if [ "$?" -ne 0 ]
then
echo "error:sqlplus $userid error in unload table $table!!"
echo "please check userid and passwd or database."
exit
fi
if [ -f lv_$table.txt ]
then
cat lv_$table.txt | grep -v "^SQL>;"|sed -e "s/ *$//g"|sed "s/$//|/g"|sed -e "s/ */|//|/g" >;${table}.unl
if [[ `grep "ORA-" $table.unl` = "" ]]; then
echo "unload table ${table}..../t/t/t/t/t ok"
else
cat ${table}.unl
err="$err ${table}"
fi
else
echo $0 error
fi
rm -f lv_$table.txt
done
################################################################
################################################################
## ½áÊ&²Ù×÷
if [[ "X$err" = "X" ]];then
echo "Unload Complete!,Thank you!"
else
echo "Unload Table $err error, please check it!"
fi
################################################################
=============================================
generate_control_file.sh
=============================================
#!/usr/bin/ksh
################################################################################
#
# Ä&&é: generate_control_file.sh
#
# ÃèÊ&: &ù¾ÝÒ&&&±íÃ&Éú³É&ñí&Ä&ØÖÆÎļ&
#
# ²ÎÊ& 1 = ÓÃ&§Ã&/ÃÜÂ&[@Ê&À&Ã&]
# ²ÎÊ& 2 = ±íÃ&
#
# ×÷Õ& Bing He
#
# ÐÞ&ļǼ
# ÈÕÆÚ ÐÞ&ÄÈË ÐÞ&ÄÃèÊ&
#
# 09/23/2003 Bing He &&ʼ±àÐ&
#
################################################################################
################################################################################
## Ö÷³ÌÐòÈ&&Ú
if [ ! $# -eq 2 ]
then
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
else
userid=$1
table=$2
fi
################################################################################
################################################################################
## Ö&ÐÐÏÂÔزÙ×÷
lv_temp="wk_${table}.test"
lv_temp1="wk_${table}.test1"
lv_temp2="wk_${table}.test2"
lv_control="${table}.ctl"
sqlplus ${userid} <<! >;/dev/null
spool ${lv_temp};
desc ${table}
spool off;
exit
!
if [ "$?" -ne 0 ]
then
echo "Error:sqlplus ${userid} error in generate control file for table ${table} !"
echo "please check userid and passwd or oracle_sid."
exit
fi
if [ -f ${lv_temp} ]
then
cat ${lv_temp}|grep -v "^SQL>;" |grep -v " Name " |grep -v " -------" |awk '{print $1}' >; ${lv_temp1}
lv_line_num=`cat ${lv_temp1} | wc -l`
lv_line_num=`expr ${lv_line_num} - 2`
lv_index=0
rm -f ${lv_temp2}
for lineinfo in `cat ${lv_temp1}`
do
if [ ${lv_index} -eq ${lv_line_num} ]
then
echo "${lineinfo}" >;>; ${lv_temp2}
else
echo "${lineinfo}," >;>; ${lv_temp2}
lv_index=`expr ${lv_index} + 1`
fi
done
else
echo "$0 error :not find ${lv_temp} file."
exit
fi
lv_str="LOAD DATA INFILE '${table}.unl' BADFILE 'bad_${table}.bad' APPEND INTO TABLE ${table} FIELDS TERMINATEd BY /"|/""
echo ${lv_str} >; ${lv_control}
echo "(" >;>; ${lv_control}
cat ${lv_temp2} >;>; ${lv_control}
echo ")" >;>; ${lv_control}
rm -f ${lv_temp}
rm -f ${lv_temp1}
rm -f ${lv_temp2}
################################################################################
=============================================
generate_execute_shell.sh
=============================================
#!/usr/bin/ksh
################################################################################
#
# Ä&&é: generate_execute_shell.sh
#
# ÃèÊ&: &ù¾ÝÒ&&&±íÃ&Éú³É&ñí&Ä×°Ôؽű¾
#
# ²ÎÊ& 1 = ÓÃ&§Ã&/ÃÜÂ&[@Ê&À&Ã&]
# ²ÎÊ& 2 = ±íÃ&
#
# ×÷Õ& Bing He
#
# ÐÞ&ļǼ
# ÈÕÆÚ ÐÞ&ÄÈË ÐÞ&ÄÃèÊ&
#
# 09/23/2003 Bing He &&ʼ±àÐ&
#
################################################################################
################################################################################
## Ö÷³ÌÐòÈ&&Ú
if [ ! $# -eq 2 ]
then
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
else
userid=$1
table=$2
fi
################################################################################
################################################################################
## ¾Ö²&±&Á&&¨Ò&Ç&Óò
lv_rows=10000
lv_bindsize=8192000
lv_readsize=8192000
################################################################################
################################################################################
## Éú³ÉÖ&Ðнű¾Îļ&
echo "sqlldr ${userid} control=${table}.ctl rows=${lv_rows} bindsize=${lv_bindsize} readsize=${lv_readsize} log=log_${table}.log bad=bad_${table}.bad direct=true" >; load_${table}.sh
################################################################################
=============================================
--------------------------------------------------------------------------------
rollingpig 回复于：2003-09-24 08:14:00
原创？还是转载？
--------------------------------------------------------------------------------
棉花糖 回复于：2003-09-24 08:33:26
我还是关心你贴的脚本文件里的乱码，呵呵，如何解决？
--------------------------------------------------------------------------------
jack79_cn 回复于：2003-09-24 09:17:57
好﹐收下
--------------------------------------------------------------------------------
playboy 回复于：2003-09-24 10:12:10
乱码情况我很奇怪，我把代码重新贴了，如果好了，请斑竹更新代码部分，谢了。
代码中unload.sh是参照本版中的一篇文档，其余为自己写的。
load.sh
#!/usr/bin/ksh
################################################################################
#
# 模块: load.sh
#
# 描述: 根据一个数据库或表名来装载对应的数据文件
#
# 参数 1 = 用户名/密码[@实例名]
# 参数 2 = 表名(可选)
#
# 作者 Bing He
#
# 修改记录
# 日期 修改人 修改描述
#
# 09/23/2003 Bing He 开始编写
#
################################################################################
lv_temp1="wk.test1"
f_get_tables()
{
rm -f ${lv_temp1}
sqlplus ${userid} <<! >;/dev/null
set colsep $sep;
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool ${lv_temp1};
select table_name from user_tables;
spool off;
exit
!
if [ "$?" -ne 0 ]
then
echo "Error:sqlplus ${userid} error in load for ${userid} !"
echo "please check userid and passwd or oracle_sid."
exit
fi
if [ -f ${lv_temp1} ]
then
lv_tables=`cat ${lv_temp1} |grep -v "^SQL>;" | tr -d ' '`
else
echo "Error:${lv_temp1} file not found!"
exit
fi
rm -f ${lv_temp1}
}
################################################################################
## 主程序入口
lv_no=$#
case ${lv_no} in
1 )
userid=$1
f_get_tables;
;;
2 )
userid=$1
lv_tables=$2
;;
* )
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
;;
esac
for lv_table in ${lv_tables}
do
if [ ! -f ${lv_table}.unl ]
then
echo "Error:${lv_table}.unl file not found!"
else
generate_control_file.sh ${userid} ${lv_table}
generate_execute_shell.sh ${userid} ${lv_table}
sh load_${lv_table}.sh
rm -f ${lv_table}.ctl
rm -f load_${lv_table}.sh
fi
done
################################################################################
unload.sh
#!/usr/bin/ksh
################################################################################
#
# 模块: unload.sh
#
# 描述: 根据一个数据库或表名来卸载数据并生成对应的数据文件
#
# 参数 1 = 用户名/密码[@实例名]
# 参数 2 = 表名(可选)
#
# 作者 Bing He
#
# 修改记录
# 日期 修改人 修改描述
#
# 09/23/2003 Bing He 开始编写
#
################################################################################
lv_sep='|' # --分隔符，可以修改成自己想要的分隔符，如'|'
lv_temp1="unload.temp1"
f_get_tables()
{
rm -f ${lv_temp1}
sqlplus ${userid} <<! >;/dev/null
set colsep ${lv_sep};
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool ${lv_temp1};
select table_name from user_tables;
spool off;
exit
!
if [ "$?" -ne 0 ] ; then
echo "sqlplus $userid error in get table name <"$?">;!!"
echo "please check userid and passwd or database."
exit
fi
if [ -f ${lv_temp1} ]
then
lv_tables=`cat ${lv_temp1} |grep -v "^SQL>;" | tr -d ' '`
else
echo "Error:${lv_temp1} file not found!"
exit
fi
rm -f ${lv_temp1}
}
################################################################
## 主程序入口
lv_no=$#
case ${lv_no} in
1 )
userid=$1
f_get_tables;
;;
2 )
userid=$1
lv_tables=$2
;;
* )
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
;;
esac
################################################################
################################################################
## 执行下载操作
for table in ${lv_tables}
do
rm -f lv_$table.txt
sqlplus ${userid} <<! >;/dev/null
set colsep ${lv_sep};
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool lv_$table.txt;
select * from $table;
spool off;
!
if [ "$?" -ne 0 ]
then
echo "error:sqlplus $userid error in unload table $table!!"
echo "please check userid and passwd or database."
exit
fi
if [ -f lv_$table.txt ]
then
cat lv_$table.txt | grep -v "^SQL>;"|sed -e "s/ *$//g"|sed "s/$//|/g"|sed -e "s/ */|//|/g" >;${table}.unl
if [[ `grep "ORA-" $table.unl` = "" ]]; then
echo "unload table ${table}..../t/t/t/t/t ok"
else
cat ${table}.unl
err="$err ${table}"
fi
else
echo $0 error
fi
rm -f lv_$table.txt
done
################################################################
################################################################
## 结束操作
if [[ "X$err" = "X" ]];then
echo "Unload Complete!,Thank you!"
else
echo "Unload Table $err error, please check it!"
fi
################################################################
generate_control_file.sh
#!/usr/bin/ksh
################################################################################
#
# 模块: generate_control_file.sh
#
# 描述: 根据一个表名生成该表的控制文件
#
# 参数 1 = 用户名/密码[@实例名]
# 参数 2 = 表名
#
# 作者 Bing He
#
# 修改记录
# 日期 修改人 修改描述
#
# 09/23/2003 Bing He 开始编写
#
################################################################################
################################################################################
## 主程序入口
if [ ! $# -eq 2 ]
then
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
else
userid=$1
table=$2
fi
################################################################################
################################################################################
## 执行下载操作
lv_temp="wk_${table}.test"
lv_temp1="wk_${table}.test1"
lv_temp2="wk_${table}.test2"
lv_control="${table}.ctl"
sqlplus ${userid} <<! >;/dev/null
spool ${lv_temp};
desc ${table}
spool off;
exit
!
if [ "$?" -ne 0 ]
then
echo "Error:sqlplus ${userid} error in generate control file for table ${table} !"
echo "please check userid and passwd or oracle_sid."
exit
fi
if [ -f ${lv_temp} ]
then
cat ${lv_temp}|grep -v "^SQL>;" |grep -v " Name " |grep -v " -------" |awk '{print $1}' >; ${lv_temp1}
lv_line_num=`cat ${lv_temp1} | wc -l`
lv_line_num=`expr ${lv_line_num} - 2`
lv_index=0
rm -f ${lv_temp2}
for lineinfo in `cat ${lv_temp1}`
do
if [ ${lv_index} -eq ${lv_line_num} ]
then
echo "${lineinfo}" >;>; ${lv_temp2}
else
echo "${lineinfo}," >;>; ${lv_temp2}
lv_index=`expr ${lv_index} + 1`
fi
done
else
echo "$0 error :not find ${lv_temp} file."
exit
fi
lv_str="LOAD DATA INFILE '${table}.unl' BADFILE 'bad_${table}.bad' APPEND INTO TABLE ${table} FIELDS TERMINATEd BY /"|/""
echo ${lv_str} >; ${lv_control}
echo "(" >;>; ${lv_control}
cat ${lv_temp2} >;>; ${lv_control}
echo ")" >;>; ${lv_control}
rm -f ${lv_temp}
rm -f ${lv_temp1}
rm -f ${lv_temp2}
################################################################################
generate_execute_shell.sh
#!/usr/bin/ksh
################################################################################
#
# 模块: generate_execute_shell.sh
#
# 描述: 根据一个表名生成该表的装载脚本
#
# 参数 1 = 用户名/密码[@实例名]
# 参数 2 = 表名
#
# 作者 Bing He
#
# 修改记录
# 日期 修改人 修改描述
#
# 09/23/2003 Bing He 开始编写
#
################################################################################
################################################################################
## 主程序入口
if [ ! $# -eq 2 ]
then
echo "Usage: $0 <userid/passwd[@connection]>; <table_name>;"
exit
else
userid=$1
table=$2
fi
################################################################################
################################################################################
## 局部变量定义区域
lv_rows=10000
lv_bindsize=8192000
lv_readsize=8192000
################################################################################
################################################################################
## 生成执行脚本文件
echo "sqlldr ${userid} control=${table}.ctl rows=${lv_rows} bindsize=${lv_bindsize} readsize=${lv_readsize} log=log_${table}.log bad=bad_${table}.bad direct=true" >; load_${table}.sh
################################################################################
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
