Re: Isolation level need

Herve Meftah wrote:

> I need some informations about the transaction isolation level on
> Oracle when I use a lot of cursors on different tables.
>
> How Oracle uses cursor by Temp table or memory array ?
> I heard some tricky problems with long transaction using cursor.
>

  I'm not sure I totally understand your question but here goes. When you open a cursor Oracle
parses the query and executes it. Fetch returns rows. Now as far as the database is concerned, what the cursor can see is the database as it was at the time the query started. Oracle terms this a read consistent view. So if you open cursror A and then make a change to the database through someother means or user the A cursor would not see those changes. The read consistent view of the database will only change after cursor A is closed.

Oracle processes cursors in memory not in temp tables. The long transaction problem you may have heard of can arise as follows:

If you start a long running query Oracle imposes the read consistent view requirement. It satisfies this requirement by reading the old versions of changed information from the rollback segments. As the transactions finish which wrote the change records we need for our read consistent view a problem may arise. Oracle will try to keep that information in the rollbacks as long as possible. But this data does not belong to any open transaction. As new transactions are executed Oracle may need the areas in which our read consistent change data is stored. At this point Oracle will give these segments to the new transactions and issue a snaphot too old error to our long running select.

The solution to this problem is to size your rollback segments large enough to prevent the needed change data from being overwritten. In the case of long update transactions you will want to provide a special long rollback segment and do a SET TRANSACTION to use that particular rollback segment.

Allan Received on Sat Aug 01 1998 - 17:04:14 CDT