Re: read only tablespace

The same is not true when you take tablespace offline with normal option . I just tested that if a transaction is pending then oracle waits before marking tablespace rea only BUTTT if x-saction is pending and I issue "offline normal" it succeds . Doesn't offline normal also checkpoints datafile ?

-ak

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, July 31, 2003 9:09 AM

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

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