Oracle-L: Re: read only tablespace

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Thu, 31 Jul 2003 21:54:23 -0800
Message-ID: <F001.005C8147.20030731215423@fatcity.com>

This is very easily explained once you consider that a "transaction" can consist of many "statements" - some of which may not have been issued to Oracle at this point in time. Oracle is obviously trying to be polite by allowing already started transactions to complete.

Of course it would make complete sense to give DBA's an option like "immediate" on such commands.

Regards,

Mark.

                                                                                                                                      
                      Rachel Carmichael                                                                                               
                      <wisernet100_at_yaho        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                  
                      o.com>                   cc:                                                                                    
                      Sent by:                 Subject:  Re: read only tablespace                                                     
                      ml-errors_at_fatcity                                                                                               
                      .com                                                                                                            
                                                                                                                                      
                                                                                                                                      
                      01/08/2003 02:09                                                                                                
                      Please respond to                                                                                               
                      ORACLE-L

What we found is that it doesn't matter which tablespace the session causing the wait is accessing. As explained to me (by Dan Fink I believe), Oracle doesn't KNOW if that transaction will involve the tablespace you are trying to make read-only or if it will not. So rather than take a chance, the alter tablespace waits for all transactions executing at the time it was issued to complete.

AK <oramagic_at_hotmail.com> wrote:
> Rachel ,
> Thanks for the Reply . On checking further I found that this session
> was
> waiting on a lock from another session . But the table or object
> which was
> locked by other session does not belong to this tablespace . Then why
> does
> the read only operation waiting for the lock to release ?? Does it
> mean
> that all transactions which started before issuing "alter tablespace
> read
> only" should be finished ??
>
> -ak
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, July 30, 2003 5:44 PM
>
>
> > Ah, one I have the answer to, as I had a similar question a while
> back.
> >
> > The alter tablespace read only is waiting for every query which
> started
> > before the alter tablespace was issued to finish. Just in case one
> of
> > them might write to that tablespace and therefore fail.
> >
> > so while the database does not HAVE to be quiesced before you issue
> an
> > alter tablespace read only, it helps to prevent waits
> >
> >
> > --- Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
> > > Hi!
> > >
> > > Alter tablespace read only performs checkpoint on datafiles
> belonging
> > > to this tablespace. But 10 minutes on an idle system is
> indicating
> > > some problems.
> > >
> > > Check this: select event, p1, p2, p3, state from v$session_wait
> where
> > > sid = <sid of waiting session>;
> > >
> > > You should get some indication, on what your alter tablespace
> command
> > > is waiting.
> > >
> > > Tanel.
> > >
> > > ----- Original Message -----
> > > From: AK
> > > To: Multiple recipients of list ORACLE-L
> > > Sent: Wednesday, July 30, 2003 10:34 PM
> > > Subject: read only tablespace
> > >
> > >
> > > why does it take long time to turn a tablespace read only . I
> > > issued the command 10 mins back , its still not returned . There
> is
> > > almost no activity in db .
> > >
> > > 8.1.7.4
> > > hp-ux
> > >
> > > -ak
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! SiteBuilder - Free, easy-to-use web site design software
> > http://sitebuilder.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Rachel Carmichael
> > INET: wisernet100_at_yahoo.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: AK
> INET: oramagic_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Aug 01 2003 - 00:54:23 CDT