PL/SQL and rollback errors

From: Vincento Harris <wumutek_at_yahoo.com>
Date: 2 Aug 2003 15:47:21 -0700
Message-ID: <2fa13ee7.0308021447.14012a4@posting.google.com>

HP Unix Oracle 8.0 Enterprise Edition

I am working with 30 million rows and each time I run into ORA-1555

Oracle commits and set transaction use rollback segments have been inserted into within the cursor to no avail but ORA-1555 still shows up

I created two rollback segments minextents 20 initial next 100M and the problem still persists.

Apart from trimming the size of the files what could be done on the SQL below to avoid ORA-1555?Thanks in advance for your ideas

DECLARE
  CURSOR speculate IS

        SELECT numbers, keynumber, details, rowid
           FROM user1.details
           WHERE status is null;
        speculate_rec speculate%ROWTYPE;

BEGIN
  OPEN speculate;
  LOOP

     FETCH speculate INTO speculate_rec;
     EXIT WHEN speculate%NOTFOUND;
         UPDATE user2.details
            SET details = speculate_rec.details
         WHERE (numbers = speculate_rec.numbers
                AND keynumber = speculate_rec.keynumber);
     IF SQL%NOTFOUND THEN
        INSERT INTO user2.details


            (numbers, keynumber, details)

            VALUES


            (speculate_rec.numbers, speculate_rec.keynumber,

speculate_rec.details);
       UPDATE user1.details
         SET status = 'INSERT'
         WHERE rowid = speculate_rec.rowid;
     ELSE
       UPDATE user1.details SET status = 'UPDATE'
         WHERE rowid = speculate_rec.rowid;
     END IF;
     COMMIT;
     END LOOP;
     CLOSE speculate;

 END;
/ Received on Sat Aug 02 2003 - 17:47:21 CDT