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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Committing after every 100 deletes/updates

RE: Committing after every 100 deletes/updates

From: Baswannappa, Shiva <SXBaswan_at_dcss.com>
Date: Mon, 28 Apr 2003 10:36:45 -0800
Message-ID: <F001.0058AAF4.20030428103645@fatcity.com>


Thanks Brad

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). Received on Mon Apr 28 2003 - 13:36:45 CDT

Original text of this message

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