Unix 'wait' on 'sqlplus' exit status reliable?

From: <zuot_at_my-deja.com>
Date: Thu, 22 Jul 1999 22:12:08 GMT
Message-ID: <7n84vg$76j$1@nnrp1.deja.com>

Hi, All,

We try to delete from a table and immediately load data to it after the delete is complete. To speed up the delete we do this:

#!/bin/ksh
...

sqlplus -s user/password @del_tbl 1 >>logfile &
sqlplus -s user/password @del_tbl 2 >>logfile &
sqlplus -s user/password @del_tbl 3 >>logfile &
sqlplus -s user/password @del_tbl 4 >>logfile &
sqlplus -s user/password @del_tbl 5 >>logfile &
sqlplus -s user/password @del_tbl 6 >>logfile &
sqlplus -s user/password @del_tbl 7 >>logfile &

(where 1,2,...,7 is a day_of_week parameter that passed into del_tbl.sql, day_of_week is a field in the table has only 1-7 values. So the above 7 statement is mutually exclusive)

wait # to wait on all the above background job to finish

sqlldr ... to the same table.

---

The above scheme works fine sometime but fails same other time at the sqlldr stage due to duplicated records. This should never happen if the delete step finishs successfully.

We try to figure out this...
It appears that even though the unix 'wait' waits correctly on the return of the sqlplus's exit status. By the time sqlldr is invoked, the delete via del_tbl.sql has not yet finished up or cleaned-up (there is a commit at the end of del_tbl.sql script).

My question is: why can this happen? Wouldn't the single sqlplus ... be considered an atomic transaction? Or maybe wait on the exit status of sqlplus is not reliable. If so, what one should wait for ... the parallel delete is certainly attractive.

We're using Oracle 7.3.3.5.

Your comments are highly appreciated.

Tao Zuo

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 22 1999 - 17:12:08 CDT