Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Snapshot too old error revisited

Re: Snapshot too old error revisited

From: Alik Shapiro <fz1dmj_at_agt.gmeds.com>
Date: 1997/04/04
Message-ID: <33456B19.51AA@agt.gmeds.com>#1/1

Chrysalis wrote:
: =
 

: Richard Hansen wrote:
: :
: : Previously, someone posted a problem that they were having with the
: : ORA-01555 (Snapshot too old error). I have been having the same
: : problem and followed the advice of those who responded to the =

original
: : post. I am still occasionally having the problem and would like to
: : present my problem and beg for help.
: :
: : I am inserting about 3.5 million records from one table into another.
: : I have to process each record a bit before insertion so have declared
: : a cursor on the source table and fetch each record, process it, and
: : insert into the destination table. Every 100,000 rows I commit and
: : continue.
: :
: : The last time I ran this it blew after inserting 300,000 rows. Oh
: : yeah, in preparation for this I created a pretty big rollback segment
: : (~ 1 gig) and at the beginning of the script as well as after the
: : commit I perform a "alter session use rollback segment ..." so that I
: : can utilize this big rollback segment.
: :
: : Last week I ran this during business hours and it ran successfully. =

I
: : ran it yesterday evening (Saturday) and it ORA-1555'ed as mentioned
: : above.
: :
: : What am I missing? Any suggestions?
: :
: : Thanks in advance for any direction.
: =
 

: One approach that may help is to determine whether you have a single
: SELECT cursor that is being fetched from through the whole process. If
: so, remeber that it must read the version of the table(s) which existed
: at the start of the whole process, which means that *all* before images
: must be retained. This is by far the most common reason for the =

problem
: you describe.
: If such is the case, see if the process can be broken into discrete
: chucks (reading, say, data for one country / factory / currency at a
: time) and closing the cursor before executing the next select.
: Note that manipulating the commit frequency does not help at all in the
: above case.
: =
 

: Chrysalis.

I can say even more. Commit frequently you are asking for =93ORA-01555 =

(Snapshot too old)=94 error. After you commit your transaction you make =

the rollback segment inactive ( if the only one transaction uses this =

rollback segment ). The segment can be overwritten ( here is ORA-01555 =

if any SELECT statement uses this rollback segment for read ). If =

ORA-01555 is your major concern do not commit till you issue all INSERTs. =

 =

Hope it helps
-- =

Alik Shapiro
Oracle DBA, EDS
Phone (317)240-7757
E-MAIL : fz1dmj_at_agt.gmeds.com

The above statements and opinions are my own and do not necessarily represent those of EDS. Received on Fri Apr 04 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US