FAQ
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).

Search Discussions

  • DENNIS WILLIAMS at Apr 25, 2003 at 10:11 pm
    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
    ---------------------------------------------------------------------

    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).
  • John Blake at Apr 25, 2003 at 10:32 pm
    Assuming you have declared a cursor and are looping through it to control
    processing.
    you need to

    declare
    m_counter number :=0;
    m_commit number := 100;
    cursor x is select .......;

    begin
    for currec in x loop
    if updateing

    then
    m_counter := m_counter+1;
    update
    if m_counter = m_commit
    then
    commit;
    m_counter := 0;
    end if;

    end if;
    end loop;
    commit;
    end;

    -----Original Message-----
    Baswannappa, Shiva
    Sent: Friday, April 25, 2003 5: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: John Blake
    INET: jblake_at_arrow.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).
  • Odland, Brad at Apr 28, 2003 at 3:30 pm
    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).
  • Baswannappa, Shiva at Apr 28, 2003 at 6:36 pm
    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).
  • Connor McDonald at Apr 29, 2003 at 3:32 am
    You could always play with commit based on time as
    opposed to rows. eg

    loop
    delete/update row
    x := dbms_utility.get_time;
    if x - prev_time > n then\
    commit;
    prev_time := x;
    end if;
    end loop

    hth
    connor

    "Baswannappa, Shiva" wrote: >
    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).
    Connor McDonald
    web: http://www.oracledba.co.uk
    web: http://www.oaktable.net
    email: connor_mcdonald_at_yahoo.com

    "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

    Yahoo! Plus
    For a better Internet experience
    http://www.yahoo.co.uk/btoffer

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: =?iso-8859-1?q?Connor=20McDonald?=
    INET: hamcdc_at_yahoo.co.uk

    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).
  • Bob Metelsky at Apr 30, 2003 at 4:16 pm
    Here is a good article on this subject
    http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci895414,00.htm
    l

    bob
    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.
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Bob Metelsky
    INET: bmetelsky_at_cps92.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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 25, '03 at 9:13p
activeApr 30, '03 at 4:16p
posts7
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase