Re: alter tablespace read only hangs

"Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message news:7CqKh.11817$8U4.6613_at_news-server.bigpond.net.au...
>
>>>
>>> Hi Jonathan
>>>
>>> I think the reason is simply because Oracle has no way of guaranteeing
>>> that
>>> any current transaction would not at some point in the future need to
>>> make a
>>> change to an object in the soon to be read only tablespace.
>>>
>>> For example, it might currently be performing an update on a table in
>>> tablespace A. When finally complete, the same transaction may need to
>>> write
>>> an audit record to a table that lives in tablespace B, the tablespace
>>> being
>>> made read only. If the read only operation succeeded part way through
>>> the
>>> transaction, simply because the transaction wasn't currently making
>>> changes
>>> within tablespace B, the transaction would fail.
>>>
>>> There's no way for Oracle to know that these potential changes are
>>> coming,
>>> hence why it waits for all current transactions to complete. It
>>> considers
>>> the guaranteeing of transactions to succeed to be the lesser evil than
>>> the
>>> associated waits.
>>>
>>> Cheers
>>>
>>> Richard
>>>
>
> Actually Chuck, it doesn't quite make sense !! Playing with a "toy"
> database (9.2.0.7), I found that once a tablespace is in the process of
> being made R/O, all transactions whether started before or after the
> alter tablespace command fell over being unable to write to the
> tablespace. The tablespace being effectively R/O even if the command had
> not completed due to transaction locks.
>
> Not how I would have designed it :)
>
> Richard
>
>
>

Richard

Remember in v7 that the command used to wait until there were no active transactions on the database ? That's the "primitive" way of doing what you would like to see.

Implementing things the way you would like, every transaction that tried to update a block in a 'pending readonly' tablespace would first have to check who was trying to make the tablespace readonly and then compare the SCN of the readonly call with the SCN of the start transaction.

That could be a lot of overhead for very little benefit in a busy system, so it probably makes sense to assume that a read-only call is being done deliberately and that nothing is supposed to be updating the tablespace - hence the current strategy.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html