First, I ommitted the ;) when replying to Rachel's post. I was just
funnin...
Second, in this case TTS will not work. While we can 99.99% guarantee that
there will be no tx against the tablespace, there will be active tx in the
database and we cannot guarantee that they will not cause the RO command to
wait.
Interestingly, it is possible to offline the tablespace while there are
active tx (even against objects in the ts). If you can offline and then
online with active tx, why not alter it to RO? Perhaps, once again, I am
missing the obvious.
Dan
-----Original Message-----
Sent: Thursday, October 10, 2002 8:23 PM
To: Multiple recipients of list ORACLE-L
yes it's relevant, it explains why you can't use a consistent export
only to move the data and have to copy the tablespace as well. It also
explains why the tablespace has to be in read-only mode.
- "Fink, Dan" <Dan.Fink_at_mdx.com> wrote:
> That information is not relevant and should be ignored. I would
> expect YOU
> to know as much!
>
> -----Original Message-----
> Sent: Thursday, October 10, 2002 1:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
> ah! transportable tablespaces? you did't SAY that
>
>
> --- "Fink, Dan" <Dan.Fink_at_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_at_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_at_SLAC.Stanford.edu <mailto:ian_at_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_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.com
> > --
> > Author: Fink, Dan
> > INET: Dan.Fink_at_mdx.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
> services
> >
> ---------------------------------------------------------------------
>
=== message truncated ===
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_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.com
--
Author: Fink, Dan
INET: Dan.Fink_at_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_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 Fri Oct 11 2002 - 13:14:20 CDT
