导出指定数据库表的内容
时间：2009-08-25 来源：w1_xiao
#!/usr/bin/ksh
###################################################################
# unload.sh
#
# 描述: 根据一个数据库或表名来卸载数据并生成对应的数据文件
#
#####################################################################
lv_sep='|' # --分隔符，可以修改成自己想要的分隔符，如'|'
lv_temp1="unload.temp1" f_get_tables()
{
rm -f ${lv_temp1}
sqlplus name/passwd@oracle <<! >/dev/null
set colsep ${lv_sep};
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 10000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool ${lv_temp1};
select table_name from user_tables where table_name like 'T\_%' escape '\' ;
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
0 )
f_get_tables;
;;
1 )
lv_tables=$1
;;
* )
echo "Usage: $0 "
exit
;;
esac ######################################################### ########################################################## for table in ${lv_tables}
do
rm -f lv_$table.txt
sqlplus name/passwd@oracledb <<! >/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
printf "unload table %-30.30s\t\t ok\n" ${table}...
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
###################################################################
# unload.sh
#
# 描述: 根据一个数据库或表名来卸载数据并生成对应的数据文件
#
#####################################################################
lv_sep='|' # --分隔符，可以修改成自己想要的分隔符，如'|'
lv_temp1="unload.temp1" f_get_tables()
{
rm -f ${lv_temp1}
sqlplus name/passwd@oracle <<! >/dev/null
set colsep ${lv_sep};
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 10000;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
spool ${lv_temp1};
select table_name from user_tables where table_name like 'T\_%' escape '\' ;
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
0 )
f_get_tables;
;;
1 )
lv_tables=$1
;;
* )
echo "Usage: $0 "
exit
;;
esac ######################################################### ########################################################## for table in ${lv_tables}
do
rm -f lv_$table.txt
sqlplus name/passwd@oracledb <<! >/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
printf "unload table %-30.30s\t\t ok\n" ${table}...
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
相关阅读 更多 +
