Re: read only tablespace

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