自動sqlldr腳本

測試了這個腳本（收集自網絡），很不錯，做異構資料庫遷移或oracle大版本之間遷移非常有用。

在config.cfg裡面配好相應的使用者和表名就可得到相應的select語句和該表的控制檔案，運作generate.sh可以得到該表的資料檔案，運作sqlldr可以把生成的資料檔案插入到目标庫表中。目前功能有限，一次隻能生成一個使用者下的表。

config--配置檔案

script --儲存資料導出sql

unl --資料檔案

ctl --控制檔案

log --日志檔案

bad --sqlldr導入錯誤資料

temp --臨時檔案

./temp.sh--生成源系統使用者建表字段sql

#!/bin/sh

DB_USER=test

DB_PWD=test

DB_TNS=orcl2

sqlplus -s $DB_USER/$DB_PWD@$DB_TNS<<EOF

set echo off

set feedback off

set pagesize 0

set heading off

set linesize 120

set trimspool on

spool temp.txt

select table_name from user_tables;

spool off

EOF

./config/config.cfg：--源系統使用者配置檔案

#################################

# This config file is prepare for the script run.sh

#

# connect database username and password and the tnsname

# this parameter can help you to login the database

username=test

passwd=test

tnsname=orcl2

# the owner of target table which is the table's owner

owner=dbo

# the table that you want to get its record to generate data file

# you can add the table name and separate them with ','

#awk '{a[NR]=$1}END{for (i=1;i<=NR;i++) print a[i]}' $file

./config/sqlldr.cfg：--目标系統使用者配置檔案

#######################################

# This config file is prepare for the script sqlldr.sh

# the target user and password and tnsname

# you can sql load the data file into the username/passwd@tnsname

username=dbo

passwd=dbo

tnsname=tcell

./run.sh：--從源系統表中生成控制檔案及sql導出腳本

#run.sh

############################################################

## parameter initialization

username=""

passwd=""

tns=""

owner=""

table=""

## read the config file

## config file directory is ./config/config.cfg

eval $(awk -F"=" '{

if ($1~/username/) print "username="$2;

if ($1~/passwd/) print "passwd="$2;

if ($1~/tnsname/) print "tns="$2;

if ($1~/owner/) print "owner="$2;

#if ($1~/tables/) print "tables="$2

}' ./config/config.cfg)

## generate the script file get_unl file

## to get the table's unl file

rm -rf temp

mkdir temp

rm -rf script

mkdir script

rm -rf ctl

mkdir ctl

rm -rf unl

mkdir unl

file=./temp.txt

tables=($(cut -f1 $file))

echo "set heading off

set head off

set linesize 3000

set termout off

set trims on" > script/get_unl.sql

for ((i=0;i<${#tables[@]};i++))

do

echo "spool ./temp/${tables[i]}.tmp

select table_name,column_name,data_type from all_tab_cols where owner=upper('$username') and table_name=upper('${tables[i]}') orde

r by column_id;

spool off" >> script/get_unl.sql

done

echo "exit" >> script/get_unl.sql

#echo $tables | awk -F"," '{

# print "set heading off"

# print "set head off"

# print "set pagesize 0"

# print "set linesize 3000"

# print "set feedback off"

# print "set termout off"

# print "set trims on"

# for(i=1;i<=NF;i++) {

# print "spool ./temp/"$i".tmp"

# print "select table_name,column_name,data_type from all_tab_cols where #owner=upper('\'''$owner''\'') a

nd table_name=upper('\''"$i"'\'') order by column_id;"

# print "spool off"

# }

# print "exit"

# }' > script/get_unl.sql

echo "the script of get unl had been generated :"

ls script | grep "get_unl"

echo ""

## run the sql script

## generate the temp file of the table and column information

sqlplus -S $username/$passwd@$tns @script/get_unl.sql

echo "the tempfile of table and column information had been generate :"

ls temp

## generate the sql script which is select from the table

for loop in $(ls temp)

#table_name=`echo $loop | awk -F"." '{print $1}'`

table_name=$(basename $loop .tmp)

new_file_select="script/"$owner"."$table_name".sql"

new_file_ctl="ctl/"$owner"."$table_name".ctl"

rm -f new_file_select

awk 'BEGIN{

print "set heading off"

print "set head off"

print "set pagesize 0"

print "set linesize 3000"

print "set feedback off"

print "set termout off"

print "set trims on"

print "spool unl/'$owner'.'$table_name'.unl"

print "select "

}{

if (NR==1){

if($3~/DATE/) print "to_char("$2",'\''yyyymmddHH24miss'\'')"

else print $2

}

else {

if($3~/DATE/) print "||'\''|'\''||to_char("$2",'\''yyyymmddHH24miss'\'')"

else print "||'\''|'\''||"$2

}

END{

print "||'\''|'\''"

print "from '$username'."$1";"

print "spool off"

print "exit"

}' temp/"$loop" > $new_file_select

print "load data"

print "badfile '\'bad/$table_name.bad\''"

print "truncate into table '$table_name'"

print "fields terminated by '\''|'\''"

print "trailing nullcols"

print "("

if($3~/DATE/) print ","$2" date '\''yyyymmddHH24miss'\''"

else print ","$2

print ")"

}' temp/"$loop" > $new_file_ctl

echo "the select script had been generated :"

ls script | grep ^[^get_unl]

echo "the sqlldr control file had been generated :"

ls ctl | grep [sql$]

# delete the temporary directory temp

#rm -rf temp

./generate.sh--生成資料檔案

#generate_unl.sh

## this shell script is in accordance with the script file in the directory script

## to generate the data file

################################

for loop in `ls script | grep ^[^get_unl]`

echo "the $loop is generating...please wait..."

sqlplus -s test/test@orcl2 @script/$loop

./sqlldr.sh--将資料sqlldr到目标系統

#sqlldr.sh

if ($1~/tnsname/) print "tns="$2;

}' config/sqlldr.cfg)

## check the log directory

if [ -d log ]

then

rm -rf log/*

else

mkdir log

echo "directory log is created"

fi

## check the bad file directory

if [ -d bad ]

rm -rf bad/*

mkdir bad

echo "-------begin load data---------"

for loop in `ls script`

if (echo $loop | grep ^[^get_unl]>/dev/null 2>&1)

#table_name=`echo $loop | awk -F"." '{print $1"."$2}'`

table_name=$(basename $loop .sql)

sqlldr $username/$passwd@$tns control=ctl/$table_name.ctl data=unl/$table_name.unl log=log/$table_name.log bad=bad/$table_name.bad

rows=50000 errors=1000 direct=true silent='(header,feedback)'

for loop in `ls log`

echo "the sqlldr log file << $loop >> contain : "

grep -E 'not load|Total|success' log/$loop | grep -v 'thread'