Re: Choose rollback segment for SQL*Loader?

try to load the data using DIRECT path and UNRECOVERABLE. that will definitely bypass all those redo log files and rollback segments.

but if the instance fail during the load period, you may have to reload the data again. Also, if there is a media failure furing the load period and you try to reuse the data block, you may have the error, data block corrupted.

Remember to backup the tablespace right after you load your data using unrecoverable, or else you may not be able to recover your database later.

Winnie

rok wrote in message <01bda71c$f683d640$410837a6_at_raghus-computer>...
>Direct Path load still will use the rollback segment if the LOAD option is
>REPLACE. If it is APPEND/INSERT then DP won't use rollback segment. REPLACE
>option still needs to delete the existing rows.
>
>raghuvir
>
>SBance <sbance_at_aol.com> wrote in article
><1998070400043700.UAA05210_at_ladder01.news.aol.com>...
>> >Does anyone know if there's a way to specify the rollback segment that
>> >SQL*Loader will use? Sort of equivalent to the 'SET TRANSACTION USE
>> >ROLLBACK SEGMENT' in SQL. Loader is blowing our normal rollback segments
>> >when loading large files but I'm reluctant to increase them because
>they're
>> >sized properly for normal operations.
>> >
>> >I'm not sure even why the limit is being reached. Loader appears to
>COMMIT
>> >every few records which means that the rollback should be realeased
>surely?
>> >I'm wondering if it is anything to do with the "REPLACE" clause which
>> >deletes all records from the table before loading new data in. I plan to
>> >test this by truncating the table first and using the "INSERT" clause
>> >instead, but I'd still like Loader to use a specific rollback segment.
>> >
>> >Other than taking all the normal rollback segments offline while loader
>> >runs, I can't find any way of accomplishing this. Any ideas?
>> >
>> >
>>
>> Have you tried the direct path option? Since you're deleting all the
>data and
>> reloading the entire table anyway, I'd recommend creating a script that
>drops
>> and recreates the table, then populates it with a direct path load.
>Direct
>> path loads are VERY fast--many times faster then conventional path
>loads--and
>> you don't have to worry about rollback segments because they don't use
>them
>> anyway. The only tradeoff is that your input file has to be very clean
>because
>> just about any processing error will halt a DP load.
>>
>> Hope this helps.
>>
Received on Mon Jul 06 1998 - 22:45:37 CDT