Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: alter tablespace read only hangs

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Thu, 15 Mar 2007 13:02:16 GMT
Message-ID: <stbKh.11542$8U4.10868@news-server.bigpond.net.au>

"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 Received on Thu Mar 15 2007 - 08:02:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US