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 -> PL/SQL and Snapshot too old

PL/SQL and Snapshot too old

From: Dan Townsend <townsend_at_ebmud.com>
Date: 1997/03/12
Message-ID: <3326FA5D.3421@ebmud.com>#1/1

I thought I understood 'Snapshot too old' from my earlier incarnation as a DBA, but this one seems odd. We have a nightly process that first deletes about 100,000 rows from a (much larger) table, then inserts rows to replace them (refreshing an external data source). To avoid blowing out the rollback segments, our developer performs the deletes in batches that are committed (no problems), then does the inserts in a PL/SQL loop that commits every 'n' records.

The 'n' records had been set to 20,000. We started hitting the limits of the rollback tablespace free space, so cut it back to 10,000. It ran for a while, then complained again. We continued cutting it back. Now we have it cut down to 1,000 and instead of running out of rollback space, it has started complaining about Snapshot too old (ORA-01555). This job runs with very little contention late at night. Monitoring the rollback segments indicates that they are not extending into the free space. No other process is reading the data being inserted.

We are in the process of migrating from Oracle 7.0.15 to 7.3.2 and thought the new Server version would solve the problem, but it has turned up there also.

Can anyone explain this or help us resolve it? Thanks.

+-----------------------------------------------------

| Dan Townsend, Supervising Database Architect
| EBMUD Enterprise Object Designer
| mailto:townsend_at_ebmud.com
+-----------------------------------------------------
Received on Wed Mar 12 1997 - 00:00:00 CST

Original text of this message

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