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 -> Snapshot Too Old Reported - But We Doubt It

Snapshot Too Old Reported - But We Doubt It

From: Alan <alan_at_erols.com>
Date: Mon, 2 Aug 2004 13:06:15 -0400
Message-ID: <2n7ak8Fr1bvqU1@uni-berlin.de>


Situation:

Long running update (2 hrs, 10 min) across a dblink

Both servers Windows 2000, Oracle 8.1.7.4.1

Problem:

For the past few months, the query has completed with no problems. Lately, it will often/usually (but not always) fail with a "snapshot too old" error:

ORA-01555: snapshot too old (rollback segment too small)

The message is followed by an ORA-02063, indicating that the problem is on the foreign server.

The reported RBS varies, so we doubt it is an RBS corruption.

We are pretty certain that the problem is not too small RBS. There are ~90 RBS, all sized the same:

STORAGE(INITIAL 1M

            NEXT 1M
            MINEXTENTS 20
            MAXEXTENTS 32765
            OPTIMAL 20M)

And near as we can tell, they don't get anywhere near maxing out.

The update most often fails when it is run as part of an overnight (early morning) batch job. There are no users in either system at the time. The only processing happening is the batch job on the originating server, and a hot backup on the foreign server. The batch job has a commit immediately prior to the update statement. The only processing happening is this update statement:

UPDATE insite_stage.client_data cd

set client_id =

(

SELECT el.location_id

FROM customer_at_pacs c,

entitylocation_at_pacs el,

booinst_at_pacs booa,

booinst_at_pacs boob,

boinstli_at_pacs boli,

boinstan_at_pacs bo

WHERE el.businessobjectnum = boob.businessobjectnum

AND el.busobjsequence = boob.databusinessobjsequence

AND el.objectnum = boob.objectnum

AND boob.objecttypeid = 'ENTITYLOCATION'

AND boob.objectnum = boli.link_objectnum

AND boob.businessobjectnum = boli.link_bobjectnum

AND boob.busobjsequence = boli.link_busobjsequence

AND boli.objectnum = booa.objectnum

AND boli.businessobjectnum = booa.businessobjectnum

AND boli.busobjsequence = booa.busobjsequence

AND boli.link_viewtype = 'INSURER'

AND booa.parent_objectnum = 0

AND booa.objecttypeid = 'SERVICERSO'

AND booa.businessobjectnum = c.businessobjectnum

AND booa.busobjsequence = c.busobjsequence

AND c.businessobjectnum = bo.businessobjectnum

AND c.busobjsequence = bo.busobjsequence

AND bo.busobjstatus IN ('ACTIVE','BOUNDAMEND')

AND cd.co_tin = c.federal_employer_no

AND c.federal_employer_no > 0

AND ROWNUM = 1 )

WHERE client_ID IS NULL

OR length(client_id) < 17

;

Notes just FYI: all of the "boo*" tables are object linking tables. The real data is in customer and entitylocation (refers to the OO entity, not an ER entity), but it is necessary to go through the other tables to obtain the correct "instance" of the object. I did not create this design. And please don't ask about ROWNUM = 1. We get the correct data when it does actually finish.

When we run the update during the day (when users are on the system), it sometimes works and sometimes fails.

We searched Metalink, but nothing we could find addressed the problem.

Any ideas what is wrong and how to solve it? TIA. Received on Mon Aug 02 2004 - 12:06:15 CDT

Original text of this message

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