Re: Read-only tablespaces and the undo log

In article <Xns96025504E87FFSunnySD_at_68.6.19.6>, IANAL_VISTA says...
>
>"Peter" <peter.paton_at_transdim.com> wrote in
>news:1108827320.108967.186700_at_c13g2000cwb.googlegroups.com:
>
>> In the past I've received a "snapshot too old" error while executing a
>> select statement against a large table. I was told that this was a
>> result of the database using the rollback segment (on 8i) to provide
>> read consistency on the table. Was that incorrect? (This issue did
>> seem to resolve itself when we positioned the table in a read-only
>> tablespace.)
>>
>>
>
>You can get ORA-01555 while doing a SELECT, but only when another session
>CHANGES data and then does a COMMIT. After the COMMIT Oracle is free to
>overwrite the data which would provide the SELECT with a read consistent
>view of the data. My previous point was that the SELECT does not "generate"
>any undo or redo; however it may try to use what is in the RBS to achive
>a read consistent view of the data. Placing a table into a readonly TS
>precludes another session from generating undo segments; hence no ORA-01555

well, not 100% true -- after a while, it will be but you can definitely get ora-1555's with a read only tablespace

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:895410916429

for a period of time. (soon it'll know that all of the blocks in there are "old enough" and not need to check the rbs anymore).

And the select can in fact generate redo easily due to block cleanouts (and auditing, and recursive sql, ... ).

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation