sql loader goes inactive

Hi,

We have 1 job running on our production which recieves file through ftp from a mainframe machine and initiates sql loader. But everytime, the sql loader goes inactive after inserting few thousand records..precisely 20k..while the file ftped has around 6-7 million records.


has any1 faced anything like this?

Would it be...the loader is being called before the ftp is complete...i tested to run sql loader while the ftp is on..it still loads..without any problem..:-(

getting errors or discards?

If you think you are getting a partial ftp file, I would ftp the main file, and then ftp a "marker" file. In your script you can check for presence of the marker file and know the main file has been sent.

nopes..nothing..no bad file..no dsc file..nothing..log file says

SQL Loader STARTED on Wed Feb 16 12:51:24 CST 2005


and thats it..

any session activity? anything in the alert log?

Hi,

Again this problem, and this happens everytime..after exactly 19000 records. And I dont have access to the alert log..but the funny part is , this loader has failed 3 times in 3 months..for only 1 function(its runs just fine for all other functions)..and it fails after exactly 19000 records:-(

If its a cronjob, then check if the job is writing any logs.
Check the timing of the sqlloader job also check when the ftp of the file completes.

regards
anandkl

Hey Anand,

Thanks for the reply.

it is writing into a log file. But it writes till exactly 19456 record and then it hangs.

Actually we let the job sleep for 30 mins before actually loading the file using sql loader to be actually sure that the file has been transfered before we start the loader.

yet the job hangs.

and another thing..

the sql loader log file..it writes noting into it..a 0 byte file is created ..even though 19456 records are inserted into the table through the loader.

Hi,

Just before the sqlloader starts, there is a truncate command for the same table..and they have set the parameter on for output as

set serveroutput on size 1000000;

and what does v$session_wait say?

and have you checked if your SQL*Loader session still exists?