Oracle-L: RE: Committing after every 100 deletes/updates

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Fri, 25 Apr 2003 14:11:40 -0800
Message-ID: <F001.00589F0C.20030425141140@fatcity.com>

Shiva

SQL doesn't have that terminology. There are other ways: 1. Use SQL*plus COPY command. This can perform most SQL tasks and can commit at regular intervals, if desired.
2. Write a PL/SQL script that opens a cursor and loops through the records, committing at the desired interval.
3. Use an editor to create a script with repeated SQL statements and commits between each statement. For example, if you want to delete a million rows from a table, but you feel you can only delete 100,000 at a time, then

delete from table where rownum < 100000; commit;
delete from table where rownum < 100000; and so on.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----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: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------