將數(shù)據(jù)導(dǎo)出成為文本格式的備份的shell腳本
2024-07-21 02:40:12
供稿：網(wǎng)友
#將數(shù)據(jù)庫(kù)中表的內(nèi)容導(dǎo)出成為一個(gè)文本格式的shell腳本
#有兩種使用方法（假設(shè)這個(gè)腳本的名字叫做unload):
# 1.將一個(gè)用戶中所有的數(shù)據(jù)庫(kù)表的內(nèi)容到出來(lái)：
unload userid/passwd[@connection]
# 2.只導(dǎo)出一個(gè)表的內(nèi)容：
# unload userid/passwd[@connection] table_name
#這里要感謝you的帖子，是他讓我學(xué)會(huì)了如何設(shè)置sqlplus環(huán)境，從而
#將數(shù)據(jù)庫(kù)數(shù)據(jù)分解出來(lái)。
#
#我還想寫(xiě)出一個(gè)根據(jù)數(shù)據(jù)庫(kù)中的數(shù)據(jù)字典的內(nèi)容自動(dòng)生成ctl文件的腳本，
#以便于將文本的數(shù)據(jù)庫(kù)內(nèi)容使用sqlldr導(dǎo)入到數(shù)據(jù)庫(kù)中
#請(qǐng)各位提示我可能要涉及的數(shù)據(jù)字典是哪些 ：）
#
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
