Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Snapshot too old error revisited
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