Shell script

Hi ,

I have to load data to 50+ tables using sqlloader. I have separate flatfile, control file for each table .first time i ran the sqlloader command manually for each table. But now i want to put all the sqlloder commands in a shell script and run 5 sqlloder commands parallel for 5 different tables. How would i do that.  I appreciate your help
Thank you
ajaybeldeAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
I would do it this way:

#/bin/ksh
sqlldr control=controlfile1 &
sqlldr control=controlfile2 &
sqlldr control=controlfile3 &
sqlldr control=controlfile4 &
sqlldr control=controlfile5 &
wait
sqlldr control=controlfile6 &
sqlldr control=controlfile7 &
sqlldr control=controlfile8 &
sqlldr control=controlfile9 &
sqlldr control=controlfile10 &
wait
....

That will send 5 off and then wait for those to complete before sending off the next 5.  There are ways to do that in a loop, but the commands would have to be pretty similar, or all contained in the control file.  You could loop through every file like this:

#/bin/ksh
wait_cnt=0
for f in *.ctl
do
  sqlldr control=$f &
  let wait_cnt +=1
  if [ $wait_cnt -eq 5 ] ; then
    wait
    wait_cnt=0
  fi
done
wait
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Place the 5 sql loader commands in a single script file and place an '&' after each one.  This runs it in the background.

If you always want them to run no matter what add a nohup in front:

#/bin/ksh
nohup sqlldr control=controlfile1 &
nohup sqlldr control=controlfile2 &
etc...
0
 
DavidSenior Oracle Database AdministratorCommented:
It seems a good solution, but not scalable to say, 50 tables.  After ensuring that the load was scripted to use parallel resources, I might approach using a for loop, as in:

for file in T1 T2 T3 ...T50.; do
nohup $ORACLE_HOME/bin/sqlldr control=control$file &
done

As an aside, remember to adjust your instance resources to optimize the I/O for this batch process.
0
 
Docteur_ZCommented:
Thread is closed, but in case...
On system authorizing it, I would use at command with user job queues (queues 'g' to 'z')
echo $COMMAND1 | at - q g -t now
echo $COMMAND2 | at - q h -t now
echo $COMMAND3 | at - q i -t now
echo $COMMAND4 | at - q j -t now
echo $COMMAND5 | at - q k -t now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.