Re: Snapshot too old, any way to disable consistency?

From: <michael_bialik_at_my-deja.com>
Date: Sun, 11 Jul 1999 20:08:43 GMT
Message-ID: <7matk5$4j7$1@nnrp1.deja.com>

Hi,

 As far as I know - there is no possibility to "cheat"  on read-consistency issues.
 I used 2 solutions to bypass it :

1. Try to improve your query in order to cut it's execution time.
2. Close the outer cursor after processing n rows and re-open it again :

    DECLARE

     i    NUMBER := 0;
     last_id NUMBER := 0;
     CURSOR emp_crs ( p_emp_id NUMBER ) IS
      SELECT * FROM emp
      WHERE emp_id > p_emp_id;

    BEGIN
      OPEN emp_crs ( last_id );
      FETCH emp_crs INTO emp_rec;
      WHILE emp_crs%FOUND LOOP
        i := i + 1;
        ... process your data
        IF i = 10000 THEN
          last_id := emp_rec.emp_id;
          CLOSE emp_crs;
          OPEN emp_crs ( last_id );
        END IF;
        FETCH emp_crs INTO emp_rec;
      END LOOP;
      CLOSE emp_crs;

    END;     Good luck. Michael.
In article <87u2rcxxgu.fsf_at_mit.edu>,
  Greg Stark <gsstark_at_mit.edu> wrote:
>
> I have a long-running "select *" query--it takes hours to process all
the
> records--and sometimes I get "snapshot too old" for some of the rows.
Is there
> any way to convince Oracle that I really don't care about consistent
reads
> here, just fetch the most recent data from that record? This is
especially
> important because I'm considering enabling discrete transactions for
the
> updates anyways, so these errors will become much more frequent.
>
> greg
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun Jul 11 1999 - 15:08:43 CDT