Re: Commit/Rollback using SQL*Loader

Be aware that SQL*Loader has a 64K memory limit (verified with Oracle Support). You cannot get more than the number of rows that will fit in a 64K mem structure. Oracle states if you need more performance -- use Direct Path.

Jurij Modic wrote in message <34df883e.45942204_at_www.sigov.si>...
>On Sun, 8 Feb 1998 13:12:13 -0800, "Murali Kazhipurath"
><murali_at_jps.net> wrote:
>
>>Is there any way to make SQL*Loader treat the whole load process as one
>>transaction. i.e., if any kind of exception (including bad records) occurs
>>during the load process, SQL*Loader should rollback the whole transaction.
>>
>>I am using the conventional method and by default the commit point under
>>this method is 62 records. I thought about bumping up this number. But
>>unfortunately, I am not in a position to know the upper limit of the
 number
>>of records that can come in at any point of time.
>
>I don't think you can prevent "good" records to be commited if there
>are som "bad" records in a bind array. Loader inserts records in
>arrays, but it seems like it treats every single record as individual
>insert transaction. So if in array of 64 records it finds 4 bad
>records, it rejects this 4 and inserts other 60. So IMHO there is no
>way to make SQL*Loader to treat the whole load job as a single
>transaction. Of course, you can allways load data into temporary
>table, check the log file for bad records and if all records were
>loaded succesfuly, insert them from temporary table into your
>destination table.
>
>If despite of this you still want to load all of your records in a
>single bind array, you can specify an extremely large BINDSIZE
>parameter (as large as available memory permits) and an extremely
>large ROWS parameter (largere then you expect your number of records
>to be inserted will ever be). That way all of your records will be
>read into single bind aray and then iserted, although the performance
>of this might be questionable.
>
>>Murali Kazhipurath
>>(murali_at_jps.net)
>
>Regards,
>============================================================
>Jurij Modic Republic of Slovenia
>jurij.modic_at_mf.sigov.mail.si Ministry of Finance
>============================================================
>The above opinions are mine and do not represent any official
>standpoints of my employer
Received on Wed Feb 11 1998 - 00:00:00 CST