Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Snapshot Too Old Reported - But We Doubt It
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