Re: Oracle Snapshot Too Old during programatic load

Ron,

        With an index, you also increase the amount of undo that must be generated. It will also increase the amount of time that is required to process the load. Either of these can increase the risk of ORA-01555.

        ORA-01555 means that the information required to reconstruct a read consistent view of the data in question is missing. This can be for several reasons. Either the actual undo data has been lost (overwritten or in a deallocated extent) or the link to the data has been overwritten (transaction table entry). In terms of cause #1, is optimal set too low for the rbs? In v$rollstat, how many extends and shrinks are you seeing? In terms of cause #2, what is your db_block_size, how many other transactions are running at the same time and how many rbs do you have?         

Daniel Fink

"Smith, Ron L." wrote:
>
> That's just sidestepping the problem. I agree it is an easy fix but
> there should be a way to diagnose and correct the problem. I have done
> everything suggested in the Oracle papers but nothing seems to work.
> There must be something I am missing.
>
> Thanks!
> Ron
>
> -----Original Message-----
> Sent: Monday, July 28, 2003 11:19 AM
> To: Multiple recipients of list ORACLE-L
>
> Smith, Ron L. scribbled on the wall in glitter crayon:
>
> > I asked this last week, but I have a little more info this week.
> >
> > I have a table that gets programmatically loaded each night. The
> > table needs to have an index added for reporting purposes. However,
> > when I add the index to the table in the TEST database I start getting
>
> > Snapshot Too Old errors. I added 25% more space to the RBS tablespace
>
> > but it didn't do any good. I also tried reducing the frequency of
> > commits as suggested in Oracle papers but I still get the same error.
>
> > If I remove the index, the load works fine.
> >
> > Any ideas?
>
> create the index after the load?;-) that's easily done or am i missing
> something?
>
> --
> Bill "Shrek" Thater ORACLE DBA BAARF Party member #25
> william.thater_at_carrier.utc.com
> ------------------------------------------------------------------------
> Science is a wonderful thing if one does not have to earn one's living
> at it. - Albert Einstein
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thater, William
> INET: William.Thater_at_carrier.utc.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: Smith, Ron L.
> INET: rlsmith_at_kmg.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: Daniel Fink
  INET: daniel.fink_at_sun.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).



text/x-vcard attachment: Card for Daniel Fink