將數據導出成為文本格式的備份的shell腳本
2024-07-21 02:40:12
供稿：網友
#將數據庫中表的內容導出成為一個文本格式的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 <userid/passwd@connection> <table_name>"
exit
echo /c "Userid:"
read userid1
echo /c "Passwd:"
echo off
read passwd
userid=$userid1$passwd
echo on
else
userid=$1
fi
if [ "X$2" = "X" ]; then
load_table;
if [[ "X$tables" = "X" ]];then
echo "no table in user $userid"
exit
fi
else
tables=$2
fi
for table in $tables
do
rm -f wk_$table.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 wk_$table.txt;
select * from $table;
spool off;
" sqlplus $userid >/dev/null
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 wk_$table.txt ]]
then
cat wk_$table.txt grep -v "^SQL>" >$table.txt
sed -e "s/ *$//g" $table.txt >wk_$table.txt
mv wk_$table.txt $table.txt
if [[ `grep "ORA-" $table.txt` = "" ]]; then
echo "unload table $table..../t/t/t/t/t/t ok"
else
cat $table.txt
err="$err $table"
fi
else
echo $0 error
fi
done
if [[ "X$err" = "X" ]];then
echo unload complete!
else
echo "unload table $err error, please check it!"
fi
