Re: ORA-01555 on a long select after many small txs from the same connection ?

Thanks everyone for the their input.

I've made several changes to my application (based mainly upon recommendations made by Oracle documentation supplied by Thomas Kyte) - the logic is now:

// BEGIN TRANSACTION SET TRANSACTION USE ROLLBACK SEGMENT R03 CREATE CURSOR C1 FOR SELECT * FROM NLTRANM ORDER BY PRIMARY_KEY_COLS OPEN CURSOR C1 FETCH NEXT C1 INTO NLTRANM_ROW WHILE (SUCCESSFUL)
{

    SELECT * FROM NLTRANEXTM WHERE PKEY = :1     UPDATE NLTRANEXTM SET X,Y,Z WHERE ROWID = :1

    FETCH NEXT C1 INTO NLTRANM_ROW                   <<<<< FAILS HERE STILL

COMMIT TRANSACTION I ensure theapplication runs in a large rollback segment (R03). However, I still get a snapshot too old error - on a fetch from the "nltranm" table. However, the error indicates that it is in fact rollback segment R01 that cannot supply the "rolled back" information.

Background Information

The nltranm table has 300,000 rows - built up over several months. The nltranm table is not modified within this application, nor by any other application at the point of failure (this is a single user test environment).

Does the above "snapshot too old - rollback segment R01" indicate a "DELAYED BLOCK CLEANOUT" problem. And if so can anyone explain how DELAYED BLOCK CLEANOUT works ? I understand the basic mechanisms of marking the data page with a reference to the rollback segment holding the before image, add transaction details to the data page and rollback segment transaction table, modifying the transaction table on commit and leaving the data page to be cleaned up by the next user to visit the page.

What I don't understand/have details on is:

1. Does the "next user" to access the "uncommitted" page always have to clean it up - or is it based somehow on the SCN number of this users current command and the SCN number associated with the modification to the page ?
2. If the rollback segment information has been overwritten (as is probably the problem I'm seeing), what happens to the "uncommitted" page - is it marked as committed anyway ?
3. In my situation, the SCN of the cursor accessing the nltranm must be far greater than the SCN of any changes to the nltranm table - therefore why do I still get this error ?

Many thanks

Nick Rumble Received on Thu Jul 01 1999 - 08:50:34 CDT