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
Welcome back. Time to put David Bowie back on the playlist?
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Mar 15 2007 - 10:38:43 CDT
