自动生成控制文件及数据文件并完成sqlldr的自动导入

在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导出脚本

#!/bin/sh
#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 pagesize 0
         set linesize 3000
         set feedback off
         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
echo ""

############################################################
## generate the sql script which is select from the table
############################################################
for loop in $(ls temp)
do
    #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
    awk 'BEGIN{
                  print "load data"
                  print "badfile '\'bad/$table_name.bad\''"
                  print "truncate into table '$table_name'"
                  print "fields terminated by '\''|'\''"
                  print "trailing nullcols"
                  print "("
              }{
                  if (NR==1){
                               if($3~/DATE/) print ","$2" date '\''yyyymmddHH24miss'\''"
                               else print $2
                            }
                  else      {
                               if($3~/DATE/) print ","$2" date '\''yyyymmddHH24miss'\''"
                               else print ","$2
                            }
               }
          END{
                print ")"
             }' temp/"$loop" > $new_file_ctl
done

echo "the select script had been generated :"
ls script | grep ^[^get_unl]
echo ""
echo "the sqlldr control file had been generated :"
ls ctl | grep [sql$]

# delete the temporary directory temp
#rm -rf temp

 

./generate.sh--生成数据文件

#!/bin/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]`
do
    echo "the $loop is generating...please wait..."
    sqlplus -s test/test@orcl2 @script/$loop
done

 

./sqlldr.sh--将数据sqlldr到目标系统

#!/bin/sh
#sqlldr.sh

## read the config file
eval $(awk -F"=" '{
                   if ($1~/username/) print "username="$2;
                   if ($1~/passwd/) print "passwd="$2;
                   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 ]
then
    rm -rf bad/*
else
    mkdir bad
fi

## check the bad file directory

echo "-------begin load data---------"
for loop in `ls script`
do
    if (echo $loop | grep ^[^get_unl]>/dev/null 2>&1)
    then
        #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)'
    fi
done

for loop in `ls log`
do
     echo "the sqlldr log file << $loop >> contain : "
     grep -E 'not load|Total|success' log/$loop | grep -v 'thread'
     echo ""
done

echo "------end load----------------"

 

数据导入日志可在log及bad目录下查看。