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

I have an OCI application that issues a select statement to retrieve every row from a transaction table.

The application fetches a row from the result set and for each of these, it updates a corresponding row on another table (a kind of "extension" table).

Each row is processed as a separate transaction.

After processing 300,000 rows (and therefore 300,000 txs), we get the 01555 error.

Anecdotal information suggests increasing the rollback segment size allows this application to complete successfully. But this results in having to establish a very large RBS - and the whole point of making each row a separate transaction was to try to avoid this.

I've tried running this application using a 1Mb RBS and although we get the 01555 error, it doesn't seem to affect how many rows are needed to be processed - I'd expect a 1Mb RBS to cause the app to fall over very early whereas - say a 300Mb RBS to allow the app to run for must longer.

Can anyone explain what is happening here ?

Many thanks

Nick Rumble Received on Fri Jun 25 1999 - 07:24:40 CDT