alter tablespace read only hangs

[Database Administrator]

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?

[Database Administrator]

On Mar 14, 3:21 pm, 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?


Because IIRC alter tablespace read only waits for ALL transactions to
finish
.... yes it will wait for a transaction to finish even if its happening
on another
tablespace.

Anurag

[Database Administrator]

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 Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

[Database Administrator]

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

[Database Administrator]

"Jonathan Lewis" wrote in message
news:zK6dnf5_dqBky2XYnZ2dnUVZ8taknZ2d@bt.com...
>
> "Brian Peasland" wrote in message
> news:45f84582$0$16316$88260bb3@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

[Database Administrator]

Richard Foote wrote:
> "Jonathan Lewis" wrote in message
> news:zK6dnf5_dqBky2XYnZ2dnUVZ8taknZ2d@bt.com...
>> "Brian Peasland" wrote in message
>> news:45f84582$0$16316$88260bb3@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

Richard Foote wrote:
> "Jonathan Lewis" wrote in message
> news:zK6dnf5_dqBky2XYnZ2dnUVZ8taknZ2d@bt.com...
>> "Brian Peasland" wrote in message
>> news:45f84582$0$16316$88260bb3@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
>
>


Thanks guys. That makes sense.

[Database Administrator]

"Chuck" wrote in message
news:qyiKh.1482$Eg4.1109@trnddc03...
> Richard Foote wrote:
>> "Jonathan Lewis" wrote in message
>> news:zK6dnf5_dqBky2XYnZ2dnUVZ8taknZ2d@bt.com...
>>> "Brian Peasland" wrote in message
>>> news:45f84582$0$16316$88260bb3@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

[Database Administrator]

"Richard Foote" wrote in message
news:7CqKh.11817$8U4.6613@news-server.bigpond.net.au...
>
>>>
>>> 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
>
>
>

Richard

Remember in v7 that the command used to wait
until there were no active transactions on the
database ? That's the "primitive" way of doing what
you would like to see.

Implementing things the way you would like, every
transaction that tried to update a block in a 'pending
readonly' tablespace would first have to check who
was trying to make the tablespace readonly and then
compare the SCN of the readonly call with the SCN
of the start transaction.

That could be a lot of overhead for very little benefit
in a busy system, so it probably makes sense to assume
that a read-only call is being done deliberately and that
nothing is supposed to be updating the tablespace -
hence the current strategy.

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