• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

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
2 Solutions
slightwv (䄆 Netminder) 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:

nohup sqlldr control=controlfile1 &
nohup sqlldr control=controlfile2 &
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 &

As an aside, remember to adjust your instance resources to optimize the I/O for this batch process.
johnsoneSenior Oracle DBACommented:
I would do it this way:

sqlldr control=controlfile1 &
sqlldr control=controlfile2 &
sqlldr control=controlfile3 &
sqlldr control=controlfile4 &
sqlldr control=controlfile5 &
sqlldr control=controlfile6 &
sqlldr control=controlfile7 &
sqlldr control=controlfile8 &
sqlldr control=controlfile9 &
sqlldr control=controlfile10 &

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:

for f in *.ctl
  sqlldr control=$f &
  let wait_cnt +=1
  if [ $wait_cnt -eq 5 ] ; then
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
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now