Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: intermittent commit on insert ?
Sunil wrote:
> I need to insert into a table T1, data from two views V1 or V2. The name
> of the view will be passed in as a parameter to my procedure( l_view) . Also
> Each user has a limit on the number of records he can insert (say l_limit )
> . This is also passed into the procedure.
>
> I Have been thinking about dynamic sql on the lines of (
> 'insert into T1 select * from ' || l_view || ' where rownum < ' ||
> l_limit ) as row by row processing will be slow.
>
> But I am afraid of hitting the rollback segment limit. So I would like to
> commit when ever 'n' records are inserted ( again this can be passed as a
> parameter ) .
>
> Is this possible, somehow?
>
> Regards,
> Sunil.
It is possible ... you just create a counter variable ... but don't do it. Intermittent commits are a great way to destroy the performance and scalability of your database.
If you have a rollback segment issue ... enlarge the rollback segments.
Fix the problem at hand ... don't write bad code to circumvent it.
Daniel Morgan Received on Tue Jun 04 2002 - 11:05:58 CDT