RE: Making a tablespace read-only - identifying blocking tx

[Rachel Carmichael]

thanks :)

it sort of makes sense that you can offline a tablespace with an active
transaction in it. You can offline a rollback segment with an active
transaction in it, no new transactions can start in that rbs but the
one(s) there will finish.

With the tablespace, if I remember what I read in the docs correctly
(and if the docs are correct), when you offline a tablespace, the
active transactions in it continue to process as long as they do not
try to modify a block in that tablespace. Once they try that, the
transaction dies.

Hm, that doesn't sound right.. maybe it's if the blocks from that
tablespace are still in the buffer cache?

Okay, found it in the 9ir2 Concepts manual, quoted below:

When a tablespace goes offline, Oracle does not permit any subsequent
SQL statements to reference objects contained in that tablespace.
Active transactions with completed statements that refer to data in
that tablespace are not affected at the transaction level. Oracle saves
rollback data corresponding to those completed statements in a deferred
rollback segment in the SYSTEM tablespace. When the tablespace is
brought back online, Oracle applies the rollback data to the
tablespace, if needed.

When a tablespace goes offline or comes back online, this is recorded
in the data dictionary in the SYSTEM tablespace. If a tablespace is
offline when you shut down a database, the tablespace remains offline
when the database is subsequently mounted and reopened.

You can bring a tablespace online only in the database in which it was
created because the necessary data dictionary information is maintained
in the SYSTEM tablespace of that database. An offline tablespace cannot
be read or edited by any utility other than Oracle. Thus, offline
tablespaces cannot be transposed to other databases.

--- "Fink, Dan" <[EMAIL PROTECTED]> wrote:
> 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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 recipie

RE: Making a tablespace read-only - identifying blocking tx

[Fink, Dan]

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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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
> > > [EMAIL PROTECTED]  
> > > 
> > > -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 

RE: Making a tablespace read-only - identifying blocking tx

[Rachel Carmichael]

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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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
> > > [EMAIL PROTECTED]  
> > > 
> > > -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
> > > 
> > > READ ONLY if it cannot be i

RE: Making a tablespace read-only - identifying blocking tx

[Fink, Dan]

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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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
> > [EMAIL PROTECTED]  
> > 
> > -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
> > 
> > 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: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosti

RE: Making a tablespace read-only - identifying blocking tx

[Rachel Carmichael]

ah! transportable tablespaces?  you did't SAY that


--- "Fink, Dan" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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
> > [EMAIL PROTECTED]  
> > 
> > -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
> > 
> > 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: [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 'Lis

RE: Making a tablespace read-only - identifying blocking tx

[Fink, Dan]

 D'OH!

You are absolutely correct. I completely overthought the issue and missed
the obvious.

Thanks Henry

-Original Message-
Sent: Thursday, October 10, 2002 11:09 AM
To: Multiple recipients of list ORACLE-L


But can you know for sure if an open txn will hit your tablespace until the
txn is closed?

Henry

-Original Message-
Sent: Thursday, October 10, 2002 11:39 AM
To: Multiple recipients of list ORACLE-L


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" <[EMAIL PROTECTED]> 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
> [EMAIL PROTECTED] 
>
> -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
> 
> 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: [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 'ListG

RE: Making a tablespace read-only - identifying blocking tx

[Henry Poras]

But can you know for sure if an open txn will hit your tablespace until the
txn is closed?

Henry

-Original Message-
Sent: Thursday, October 10, 2002 11:39 AM
To: Multiple recipients of list ORACLE-L


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" <[EMAIL PROTECTED]> 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
> [EMAIL PROTECTED] 
>
> -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
> 
> 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: [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 se

RE: Making a tablespace read-only - identifying blocking tx

[Fink, Dan]

That would be one option, but we are trying to avoid the overhead of the
import process as we move the data to a new database. This process will
happen daily and transporting a tablespace and plugging in a datafile is
perceived to be faster (has not been tested yet).

Dan

-Original Message-
Sent: Thursday, October 10, 2002 8:14 AM
To: Multiple recipients of list ORACLE-L


Why don't you just to a consistant export?  That will not take any
transaction after the start of the export, at leasst this is how I
understand it.

Ruth.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  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).

RE: Making a tablespace read-only - identifying blocking tx

[Fink, Dan]

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" <[EMAIL PROTECTED]> 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
> [EMAIL PROTECTED]  
> 
> -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
> 
> 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: [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.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego

Re: Making a tablespace read-only - identifying blocking tx

[Ruth Gramolini]

Why don't you just to a consistant export?  That will not take any
transaction after the start of the export, at leasst this is how I
understand it.

Ruth.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, October 09, 2002 4:48 PM


> 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

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  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).

RE: Making a tablespace read-only - identifying blocking tx

[Rachel Carmichael]

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" <[EMAIL PROTECTED]> 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
> [EMAIL PROTECTED]  
> 
> -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
> 
> 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: [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).

RE: Making a tablespace read-only - identifying blocking tx

[Deshpande, Kirti]

Title: Message



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-From: MacGregor, Ian A. 
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, October 09, 2002 4:34 
PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
Making a tablespace read-only - identifying blocking tx
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
[EMAIL PROTECTED]

  
  -Original Message-From: Fink, Dan 
  [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 1:49 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  Making a tablespace read-only - identifying blocking tx
  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  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

RE: Making a tablespace read-only - identifying blocking tx

[MacGregor, Ian A.]

Title: Message



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
[EMAIL PROTECTED]

  
  -Original Message-From: Fink, Dan 
  [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 1:49 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  Making a tablespace read-only - identifying blocking tx
  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  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

Re: Making a tablespace read-only - identifying blocking tx

[Chaim . Katz]

Here's an even more kludgy guess. Maybe you can execute the alter
tablespace in the pl/sql job queue. If it runs  too long (how long is too
long?), then you know that it's waiting (i.e., failing), and you could do
something appropriate action?





"Fink, Dan" <[EMAIL PROTECTED]>@fatcity.com on 10/09/2002 04:48:54 PM

Please respond to [EMAIL PROTECTED]

Sent by:[EMAIL PROTECTED]


To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:




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




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  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).