Re: alter tablespace read only hangs

"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.

-- 
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