SQL*Loader job exits unexpectedly and causes table to be locked with NOWAIT

I have a weekly report job that I run where I have to load about 48 logs with about 750k rows of data in each log. To facilitate this, we have been using a Java job that runs SQL*Loader as an external Process (using ProcessBuilder), one after the other. Recently however, this process has been terminating abnormally during the load which is causing a lock on the table and basically causes the process to grind to a halt until we can open a ticket with the DB team to kill the session that is hung. Is there maybe a better way to handle this upload process than using SQL*Loader or is there some change I could make in either the control file or command line to stop it from dying a horrible death?

At the start of the process, I truncate the table that I'm loading to and then run this command line with the following control file:

COMMAND LINE:

C:\Oracle\ora92\BIN\SQLLDR.EXE userid=ID/PASS@DB_ID load=10000000 rows=100000 DIRECT=TRUE SKIP_INDEX_MAINTENANCE=TRUE control=ControlFile.ctl data=logfile.log

CONTROL FILE:

UNRECOVERABLE
Load DATA
INFILE *
Append
PRESERVE BLANKS
INTO TABLE MY_REPORT_TABLE
FIELDS TERMINATED BY ","
(
filler_field1 FILLER char(16),
开发者_开发技巧filler_field2 FILLER char(16),
time TIMESTAMP 'MMDDYYYY-HH24MISSFF3' ENCLOSED BY '"',
partne ENCLOSED BY '"',
trans ENCLOSED BY '"',
vendor ENCLOSED BY '"' "SUBSTR(:vendor, 1, 1)",
filler_field4 FILLER ENCLOSED BY '"',
cache_hit_count,
cache_get_count,
wiz_trans_count,
wiz_req_size,
wiz_res_size,
wiz_trans_time,
dc_trans_time,
hostname ENCLOSED BY '"',
trans_list CHAR(2048) ENCLOSED BY '"' "SUBSTR(:trans_list, 1, 256)",
timeouts,
success ENCLOSED BY '"'
)

Once all of the logs have finished loading, I rebuild the indexes on the table and then start the report process. It seems like it's just dying on random logs now, re-running the process it will fail at a different point each time.

The reasons for the UNRECOVERABLE and SKIP_INDEX_MAINTENANCE are to speed the load up. As it is, it still can take 7-12 minutes for each log to load, it's even worse without those on. Overall it's taking about 18 hours for this process to run from start to finish.

What do you mean 'dying' ? Is there anything in a log ?

If you have to get the DBAs to kill the session, the implication is that the database session is still active. If so, it is either loading, waiting or rolling back after an error. Find out from the DBAs which it is doing. If it is rolling back, you should let that complete so that the error can be returned.

I'd look at external tables rather than SQL Loader. The files will need to be accessible on the DB server, but it makes it a simpler SQL operation which may mean better error handling.

100000 are pretty low, but check with your DBA that you have enough undo for all those inserts.. ask them to keep it monitored while running the process

Also anything on the logfile.log? Also have your DBA to check for the alert.log

Regards,