#将数据库中表的内容导出成为一个文本格式的shell脚本
#有两种使用方法（假设这个脚本的名字叫做unload):
1.将一个用户中所有的数据库表的内容到出来：
unload userid/passwd[@connection]
2.只导出一个表的内容：
unload userid/passwd[@connection] table_name
#这里要感谢you的帖子，是他让我学会了如何设置sqlplus环境，从而
#将数据库数据分解出来。
#我还想写出一个根据数据库中的数据字典的内容自动生成ctl文件的脚本，
#以便于将文本的数据库内容使用sqlldr导入到数据库中
#请各位提示我可能要涉及的数据字典是哪些 ：）
sep=',' # --分隔符，可以修改成自己想要的分隔符，如'|'
load_table( ){
rm -f table1.txt
echo " 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 table1.txt;
select table_name from user_tables;
spool off;
" | sqlplus $userid >/dev/null
if [ "$?" -ne 0 ] ; then
echo sqlplus $userid error in get table name <"$?">!!
echo please check userid and passwd or database.
exit
fi
if [[ -f table1.txt ]]
then
cat table1.txt | grep -v "^SQL>" | tr -d ' ' >table.txt
rm -f table1.txt
tables=cat table.txt
rm table.txt
else
echo "get table name error"
exit
fi
}
if [ "X$1" = "X" ]; then
echo "Usage: $0
1<userid passwd@connection=""> <table_name>"
2exit
3echo \c "Userid:"
4read userid1
5echo \c "Passwd:"
6echo off
7read passwd
8userid=$userid1$passwd
9echo on
10else
11userid=$1
12fi
13
14if [ "X$2" = "X" ]; then
15load_table;
16if [[ "X$tables" = "X" ]];then
17echo "no table in user $userid"
18exit
19fi
20else
21tables=$2
22fi
23
24for table in $tables
25do
26rm -f wk_$table.txt
27echo " set colsep $sep;
28set echo off;
29set feedback off;
30set heading off;
31set pagesize 0;
32set linesize 1000;
33set numwidth 12;
34set termout off;
35set trimout on;
36set trimspool on;
37spool wk_$table.txt;
38select * from $table;
39spool off;
40" | sqlplus $userid >/dev/null
41if [ "$?" -ne 0 ] ; then
42echo error:sqlplus $userid error in unload table $table!!
43echo please check userid and passwd or database.
44exit
45fi
46
47if [[ -f wk_$table.txt ]]
48then
49cat wk_$table.txt | grep -v "^SQL>" >$table.txt
50sed -e "s/ *$//g" $table.txt >wk_$table.txt
51mv wk_$table.txt $table.txt
52if [[ `grep "ORA-" $table.txt` = "" ]]; then
53echo "unload table $table....\t\t\t\t\t\t ok"
54else
55cat $table.txt
56err="$err $table"
57fi
58
59else
60echo $0 error
61fi
62done
63if [[ "X$err" = "X" ]];then
64echo unload complete!
65else
66echo "unload table $err error, please check it!"
67fi</table_name></userid>