Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: alter tablespace read only hangs

Re: alter tablespace read only hangs

From: Chuck <>
Date: Thu, 15 Mar 2007 21:05:26 GMT
Message-ID: <qyiKh.1482$Eg4.1109@trnddc03>

Richard Foote wrote:
> "Jonathan Lewis" <> wrote in message

>> "Brian Peasland" <> wrote in message 
>> news:45f84582$0$16316$
>>> Chuck wrote:
>>>> Oracle
>>>> 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

Thanks guys. That makes sense. Received on Thu Mar 15 2007 - 16:05:26 CDT

Original text of this message