Message-ID: <F001.004E60FD.20021010112500@fatcity.com>
Date: Thu, 10 Oct 2002 11:25:00 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Rachel Carmichael <wisernet100@yahoo.com>
Subject: RE: Making a tablespace read-only - identifying blocking tx
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

ah! transportable tablespaces?  you did't SAY that


--- "Fink, Dan" <Dan.Fink@mdx.com> wrote:
> It is not the export per se that causes the problem. It is the
> copying of
> the datafile that is the issue. The tablespace must be made read only
> so
> that the datafile can be copied in a consistent version. I can
> understand
> (and support) no active tx in the tablespace, but why the whole
> (*#(&*$#
> database? If I need to take INVOICE_1999 tablespace and migrate it to
> an
> ODS, why does it matter if Joe Accountant is adding an expense report
> in the
> EXPENSE_2002 ts?
> 
> In the Oracle doc, it lists the requirements for making a ts read
> only. On
> the next page it states (verbatim from doc)
> 
> "You do not have to wait for transactions to complete before issuing
> the
> ALTER
> TABLESPACE ... READ ONLY statement. When the statement is issued, the
> target
> tablespace goes into a transitional read-only mode in which no
> further write
> operations (DML statements) are allowed against the tablespace.
> Existing
> transactions that modified the tablespace are allowed to commit or
> rollback.
> Once
> all transactions (in the database) have completed, the tablespace
> becomes
> read-only."
> 
> I love how Oracle buries a very important consideration in the very
> last
> line of a paragraph!
> 
> We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do
> have
> other options. The application architecture is such that I am pretty
> certain
> very bad things would happen if I tried to but the database in
> restricted
> mode.
> 
> Dan
> 
> -----Original Message-----
> Sent: Thursday, October 10, 2002 5:09 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> so if it's waiting for any active transaction, I guess you could put
> the database in restricted mode until existing transactions complete.
> Of course, that sort of defeats the purpose of putting it in
> read-only
> so other people can access it.
> 
> um, 9ir2 has an export parameter of "tablespace", if you want it
> "read-only" so nothing changes while you export it, how about using
> the
> consistent=y export parameter in conjunction with the tablespace
> export?
> 
> 
> --- "Deshpande, Kirti" <kirti.deshpande@verizon.com> wrote:
> > And with that correction, it seems checking for active transactions
> > (in
> > v$transaction) would address this. 
> >  
> > However, by the time one gets a 'green' light from v$transaction
> and
> > issues
> > alter tablespace... there is the slight possibility of someone
> > starting a
> > new transaction locally or just selecting over a dblink...
> >  
> > Too bad that the new 'transitional read-only' mode does not allow a
> > graceful
> > exit... Per the Admin Guide one must set compatible to < 8.1.0 to
> > make the
> > command fail...  
> >  
> > I would be interested in learning how you tackle this issue as I am
> > also
> > trying to implement TTS in some of my databases. 
> >  
> > Thanks.
> >  
> > - Kirti 
> >  
> > -----Original Message-----
> > Sent: Wednesday, October 09, 2002 4:34 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Just a slight correction it will wait until any transaction against
> > the
> > entire database, not just the tablespace is completed.
> >  
> > Ian MacGregor
> > Stanford Linear Accelerator Center
> > ian@SLAC.Stanford.edu <mailto:ian@SLAC.Stanford.edu> 
> > 
> > -----Original Message-----
> > Sent: Wednesday, October 09, 2002 1:49 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > I am creating a stored proc that will export a tablespace. One task
> > it needs
> > to perform is to place the tablespace(s) in read only mode to make
> a
> > copy.
> > Based upon the application and proc logic, there should not be any
> > transactions against objects in the ts. However, if there are, the
> > ALTER
> > TABLESPACE command will wait until the transaction is completed. I
> > would
> > rather have the ALTER TABLESPACE command fail immediately. If I
> > cannot do
> > that, I would like to be able to test for locks on objects in the
> > tablespace
> > (figured that one out, but it is rather kludgy).
> >  
> > Is there a method to force an immediate failure of ALTER TABLESPACE
> > <tsname>
> > READ ONLY if it cannot be immediatly completed?
> > Is there a clean method/proc to determine if the ALTER TABLESPACE
> > command
> > will work?
> >  
> > Dan Fink
> > 
> > 
> 
> 
> __________________________________________________
> Do you Yahoo!?
> Faith Hill - Exclusive Performances, Videos & More
> http://faith.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Rachel Carmichael
>   INET: wisernet100@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@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.com
> -- 
> Author: Fink, Dan
>   INET: Dan.Fink@mdx.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@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!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: wisernet100@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@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).