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
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
o.com> cc:
Sent by: Subject: Re: read only tablespace
[EMAIL PROTECTED]
.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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
