Yash wrote:
>
> Hi,
>
> In the application I am developing, I need to perform a bulk insert into a
> table by selecting from another table. I anticipate millions of rows in the
> source table.
>
> My question is, will the bulk insert statement try to gather all rows from
> the source in memory and then perform the insert?
>
> I want to make sure the operation does not fail due to lack of memory or
> lack of rollback segment space.
>
> Is there an option, or hint that I can give in the INSERT command to avoid
> running out of memory?
> Is there a way I can commit after INSERTing every 10000 records?
>
> Thanks in advance
The bulk collect has a "limit" clause, and once collected, obviously a
for-all will only process the number of rows in the array.

Its unlikely you'll get much benefit with an arraysize greater than
(say) 500, but some quick benchmarking will find the right size for your
environment.

If its a one-off operation, create a massive rollback and let it go in
one op. If its not a one-off, then you need that size rollback seg
anyway, so just create it and keep it

hth
connor
--
==============================
Connor McDonald

[url]http://www.oracledba.co.uk[/url]

"Some days you're the pigeon, some days you're the statue..."