9.2.0.1 on Linux and my alter statement just waited to complete without
erroring out. We had both query only (select) and DML running. when I
rebooted the database, and no one was logged in, the alter tablespace
read only completed.
if you search the archives you can find the discussion thread. It was
about 2 months ago I believe
- Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
> Hi!
>
> > 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.
>
> Do you mean DML? Or transaction?
>
> Anyway, I get following error on my 9.2.0.1 on Win2000 when making
> tablespace read only during ongoing DML.
>
> SQL> update t set a = 1;
> update t set a = 1
> *
> ERROR at line 1:
> ORA-00372: file 9 cannot be modified at this time
> ORA-01110: data file 9: 'C:\ORACLE\ORA92\ORCL\USERS01.DBF'
>
> So it seems, that at least in this version of Oracle, tablespace can
> be made
> read only during (first) DML on it. This happened when I ran an
> update
> statement first time, and alter ts read only *during* it. When I ran
> alter
> ts after update finished correctly, Oracle behaved as expected, it
> waited
> until commit or rollback to this transaction.
>
> When testing a bit more, I also got some "ORA-00603: ORACLE server
> session
> terminated by fatal error" errors in updating session with a
> tracefile.
> These issues could be fixed in latest versions though.
>
> Tanel.
>
> >
> > 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: Tanel Poder
> INET: tanel.poder.003_at_mail.ee
>
> 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 - 09:09:28 CDT
