[Oracle-l] Committing after every 100 deletes/updates

I did exactly as you suggested. Since the system
being developed is in
development and test phase, I am using commit
frequency as a environment
variable in the shell script that calls procedures
in the package. If later
we find problems like you mentioned snapshot too old
(I have come across
such messages earlier), I will change the value and
retest.

Thanks again to all of you

Regards

Shiva

-----Original Message-----
Sent: Monday, April 28, 2003 10:31 AM
To: Multiple recipients of list ORACLE-L


Be careful about how you do the loop counter.
Commiting across cursor
fetches is allowed in ORacle but it can result in a
"snapshot too old
error." PAss the loop counter commit level as a
parameter to the
procedure/package so you can test, change the commit
level if you run into
"Snapshot Too Old Errors" without having to edit and
recompile procedures.
There are some good articles on Commit frequency on
meta-link if you have
access. They would be very helpful.

If you create a cursor that opens other or nests
cursors do not commit
inside of the top most cursor. I have run into this
several times and it can
be very troublesome to resolve (mostly because the
developers insist it is
not the code but Oracle). It typically is fine in
the early rollout of a new
procedure but as data and system activity grows it
can appear
intermittently.

Use a big rollback segment (specially made one if
needed) and alter the
session to assign the process to a rollback segment
you specify. Do few
commits say every 10,000 records to start with and
do some testing to
determine the optimum commit frequency.

It gets sticky when you add DSS type processing to
production application
systems. The rollback segments are typically
configured for many small
transactions...

Brad O.





-----Original Message-----
Sent: Friday, April 25, 2003 4:13 PM
To: Multiple recipients of list ORACLE-L


Hi Gurus

Another TGIF question.

I am using 9iV2 on HP-UX R-11. my question is:

Is there a way to auto commit changes made to a
table after every say 100
records or so? I am processing 1000s of records and
want to make sure the
changes are autocommitted. the changes are made in a
procedure bundled in a
package with several other procedures.

I thought I saw a statement similar to "delete from
tab1 where
col1='col1'.... commit

not sure

or may be definition of the table can be changed in
such a way to commit
periodically!!!

Thanks in advance for all the suggestion I get

Regards

Shiva
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Baswannappa, Shiva
INET: SXBaswan_at_dcss.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Odland, Brad
INET: Brad.Odland_at_qtiworld.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Baswannappa, Shiva
INET: SXBaswan_at_dcss.com

Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
To REMOVE yourself from this mailing list, send an
E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).