Re: Read only transaction

Rupesh Kapoor wrote:
>
> Hi,
> I'm trying to enumerate a table with 3 fields, one of them indexed.
> I type the following commands *immediately after starting sqlplus monitor*
>
> set transaction read only;
> select field from table;
>
> After about 50,000 responses, I get an error 01555 snapshot too old (rollback
> segment too small) Why does oracle need rollback space for a read only
> transaction? Also, if I have exactly one client manipulating a table, can I ask
> oracle not to use rollback space (auto commit) for a read-write transaction?
>
> Thanks
> Rupesh

It's not looking for the RBS for your transaction; it's looking for other processes' RBSs. It's trying to give you a consistent view of the table your selecting from. So it has to roll back all changes that have occurred in that table since the time your transaction started, every time you do a select. Since you're never doing a commit, there could be lots of transactions that have affected the table since your transaction started. You need to do a commit once in a while, even if you're only selecting data in a read only transaction.

As far as an auto commit... I've never seen anything that would do that for you. Anyway, a transaction should be a group of DB changes that leave your data in a consistent and meaningful state, if commited; and that should all be rolled back if any one part fails. Therefore, auto commit would only make sense if each and every DB change is totally independent of other changes. This has nothing to do with whether or not the system only has a single user.
Carol Fetters
cfetters_at_iac.net Received on Wed Feb 21 1996 - 00:00:00 CET