"DJ" <nospamplease_at_goaway.com> wrote in message news:<ptXWa.3710$Kx1.52127_at_newsfep4-glfd.server.ntli.net>...
> "Vincento Harris" <wumutek_at_yahoo.com> wrote in message
> news:2fa13ee7.0308021447.14012a4_at_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;
> > /
>
>
because you are fetching across commit . Should never do a commit in an
open cursor unless you are using autonomous transactions so get rid of the
commit or the 155 will never go
Thank you very much that seemed to have done it
Received on Mon Aug 04 2003 - 13:44:12 CDT
