Re: alter tablespace read only hangs

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Fri, 16 Mar 2007 06:15:31 GMT
Message-ID: <7CqKh.11817$8U4.6613@news-server.bigpond.net.au>

"Chuck" <skilover_nospam_at_bluebottle.com> wrote in message news:qyiKh.1482$Eg4.1109_at_trnddc03...

> Richard Foote wrote:

>> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
>> news:zK6dnf5_dqBky2XYnZ2dnUVZ8taknZ2d_at_bt.com...

>>> "Brian Peasland" <dba_at_nospam.peasland.net> wrote in message
>>> news:45f84582$0$16316$88260bb3_at_free.teranews.com...
>>>> Chuck wrote:
>>>>> Oracle 9.2.0.5.
>>>>>
>>>>> Session 1 is running "alter tablespace A read only;" and is hung. The
>>>>> wait event is enqueue. The lock tree shows that session 2 is blocking 
>>>>> it
>>>>> and that the lock is on a table *not* in tablespace A. How can this 
>>>>> be?
>>>> Before a tablespace can be READ ONLY, a checkpoint needs to be 
>>>> performed.
>>>> This ensures that all changes to the data in that tablespace are 
>>>> flushed
>>>> to the tablespace before the status is changed to READ ONLY.
>>>> Unfortunately, this also means that all changes to any and all
>>>> tablespaces need to be flushed to the datafiles. So this is why you are
>>>> seeing another session blocking the ALTER TABLESPACE command. Wait long
>>>> enough, and the command will complete.
>>>>
>>>> HTH,
>>>> Brian
>>>>
>>>>
>>> Brian,
>>>
>>> The checkpoint should just be a tablespace
>>> checkpoint - Oracle doesn't need to flush
>>> blocks from other tablespaces to disk on a
>>> read-only call.  (The same happens on 'alter
>>> tablespace begin backup).
>>>
>>> Chuck,
>>>
>>> Oracle waits for all current transactions to
>>> complete before making the tablespace
>>> read-only (but doesn't stop new transactions
>>> from starting).
>>>
>>> It would certainly seem to make sense to
>>> restrict the waits only to transactions that
>>> were known to be locking tables in that
>>> tablespace - but it just doesn't work that
>>> way.  Possibly there is some subtle reason
>>> why it would be too complicated to code
>>> this; perhaps it's just a historical reason dating
>>> back to v6 and no-one has got around to
>>> updating the code.
>>>
>>>
>>> -- 
>>

>> 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 Received on Fri Mar 16 2007 - 01:15:31 CDT