Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: intermittent commit on insert ?

Re: intermittent commit on insert ?

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Tue, 04 Jun 2002 16:05:58 GMT
Message-ID: <3CFCE560.9EB15807@exesolutions.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US