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).
Received on Thu Jul 31 2003 - 11:09:30 CDT
