oracle load restart

dnat · Post by **dnat** » Tue Aug 18, 2009 5:05 am

Hi,

I have around 100 million rows to be loaded in a table as a refresh and this happens every week. If the job aborts in the middle say after loading around 70 mil rows..is there any way to re-start it and load only the remaining 30 mil rows?

miwinter · Post by **miwinter** » Tue Aug 18, 2009 5:17 am

This has been covered here before, many times as I recall. In summary, if you are loading sequentially, then you can restart at the record from point of failure. If not though, you have the complexity added of not knowing where you got to exactly, so that leaves the options of:

- Deleting the data out and starting over from row #1
- Starting over and overwriting (perhaps via an upsert)
- Starting over via referential checking between source and table (before attempting insert)

dnat · Post by **dnat** » Tue Aug 18, 2009 5:21 am

It is a full DB refresh. so i am going to go only for Oracle Bulk load..Upsert would be very time consuming. I just wanted to check the restartability in Oracle Bulk Load.

chulett · Post by **chulett** » Tue Aug 18, 2009 5:25 am

Then talk to your DBA. A direct path (DIRECT=TRUE) load generally isn't restartable, however there is a SKIP parameter available for a conventional (DIRECT=FALSE) load.

miwinter · Post by **miwinter** » Tue Aug 18, 2009 5:27 am

OK, so it's an Oracle bulk load question then... not really Datastage at all. Information that was somewhat lacking in your post

dsuser_cai · Post by **dsuser_cai** » Tue Aug 18, 2009 9:58 am

you can use the Oracle load option parameter in your job but make sure your data is clean. If you have already staged the data in a dataset ot sequential file then use bulk loading option, because i have many times faced issues with index (being unusable -due to duplicate data). So before going for a Bulk load option make sure your data is clean and you have staged it so if something happens you can you the staging file as source.