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).
Received on Mon Apr 28 2003 - 23:37:49 CDT
