Re: snapshot too old

From: x <x_at_x.hr>
Date: Thu, 26 Feb 2004 18:09:51 +0100
Message-ID: <c1l958$kuo$1@ls219.htnet.hr>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1077813117.575546_at_yasure...
> Could you please find a single document supporting any of your
> statements from:
>
> otn.oracle.com
> tahiti.oracle.com
> docs.oracle.com
> asktom.oracle.com
>
> or any other reputable source?
>
> There is not a single sentence above that appears to be valid.
>

When I said "And don't use larger rollback segments, since it won't solve your problem", I ment don't go increasing rollback segments, since there is easyer way to solve this than waisting disk space. If you don't agree, explain.

You wanted documents that support my statements, so, here goes:

Metalink, Note:18954.1
"Is the user FETCHING across a COMMIT ?? If so this breaks the ANSI standards and is not guaranteed by Oracle." "Try using an ORDER BY clause that forces a sort in the query - this should produce the row-source up front so undo is only needed while this ordered row-source is produced."

Metalink, Note:40689.1
"Ensure that the outer select does not revisit the same block at different times during the processing. This can be achieved by :

-Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially visit these data blocks. "

Metalink, Note:1005107.6

"The ORA-01555 is a typical error whereby a user transaction fails. There are a few common cases where the ORA-01555 may appear: (...)
3. Fetch across commits within an open cursor (cursors retain the "snapshot" of the query at cursor open time). "

Metalink, Note:45895.1

Cause #4:

"Fetch across commit". This is where a program performs a looping operation involving repeated reads of a datum which it is also changing and committing.
The "ORA-01555" is signaled because each read must be read consistent with the read-consistent SCN from the time the cursor was opened, and eventually we overwrite the originals.

Solution 4a:

Rewrite the program to avoid fetch across commit.

Solution 4b:

Commit less often.

Solution 4c:

Add an "order by 1" statement to the query. This creates a temp segement to hold the results of the order by and will not require consistent read. Received on Thu Feb 26 2004 - 11:09:51 CST