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 (1555) - SOLUTION ?

Re: SNAPSHOT TOO OLD (1555) - SOLUTION ?

From: Patrick Flahan <flahan_at_southeast.net>
Date: 1998/03/01
Message-ID: <6db0l8$g4m@news.southeast.net>#1/1

Hi,
I am currently working at site where it is quite common for processes to run several hours like the one you mentioned. We have also run into the same problem with having a very large cursor openfor a long period of time. Unfortunately, the only way we have found to fix this is to periodically close the cursor and then re-open it. You have to figure out a way to track what you have already selected, so you don't get the same rows again. We use an indexed field and keep track of the most recent id processed. The select might look something like this:

CURSOR curGetAmounts(pnLastIdProcessd NUMBER)

    SELECT id, effective_date, amount

         FROM bill_table
       WHERE id >= pnLastIdProcessd
      ORDER BY id;

Then each time we get a new id we place it in the variable pnLastIdProcessed so if the cursor is closed and reopened it will get rows that have not been processed.

This is a rough example and there are probably much better ways of doing but if used right it will work. If anyone has other approaches I would like to hear them too.

Patrick Flahan
flahan_at_leading.net

djose_at_att.com wrote in message <6d3sns$ibj$1_at_nnrp2.dejanews.com>...
>Hello everyone,I have a Pro*C program which churns through 4 tables and
>creates a flatfile. The program encounters oracle error code ORA-1555
>(SNAPSHOT TOOOLD ) causing it to exit.( I have an exit routine for non-zero
>return codes )The tables are as followsorderorder_detailsnameaddress
>* The program basically creates a cursor on order table which pulls out
 all
>rows in that table.* For each order it pulls out data from the other 3
>tables.* I use a host array for the FETCHES with a batch size of 2000.* I
>also use the PARALLEL QUERY OPTION.* The program currently takes about 4-5
>hours to run.* The volumes in these tables are very high and transactions
>are continously updating the above tables. My Question :Is there any
>way I can work around the snapshot too old error ?Any help with respect to
>handling this error wouldbe very much appreciated. Thanks for your time,
>Jude( djose_at_att.com )
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Sun Mar 01 1998 - 00:00:00 CST

Original text of this message

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