Oracle-L: RE: Committing after every 100 deletes/updates (Out Of Office)

From: Stephen Andert <StephenAndert_at_firsthealth.com>
Date: Mon, 28 Apr 2003 21:27:42 -0800
Message-ID: <F001.0058B949.20030428212742@fatcity.com>

I am out of office until 5/5. I am not reacheable by pager. I will be checking my voice mail and e-mail at least daily.

>>> ORACLE-L 04/28/03 21:51 >>> I am out of office until 5/5. I am not reacheable by pager. I will be checking my voice mail and e-mail at least daily.

>>> ORACLE-L 04/28/03 21:37 >>> I am out of office until 5/5. I am not reacheable by pager. I will be checking my voice mail and e-mail at least daily.

>>> ORACLE-L 04/28/03 21:05 >>> I am out of office until 5/5. I am not reacheable by pager. I will be checking my voice mail and e-mail at least daily.

>>> ORACLE-L 04/28/03 20:52 >>> I am out of office until 5/5. I am not reacheable by pager. I will be checking my voice mail and e-mail at least daily.

>>> ORACLE-L 04/28/03 20:32 >>> 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" <SXBaswan_at_dcss.com> 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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Andert
  INET: StephenAndert_at_firsthealth.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: Stephen Andert
  INET: StephenAndert_at_firsthealth.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: Stephen Andert
  INET: StephenAndert_at_firsthealth.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: Stephen Andert
  INET: StephenAndert_at_firsthealth.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: Stephen Andert
  INET: StephenAndert_at_firsthealth.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 Tue Apr 29 2003 - 00:27:42 CDT