Oracle-L: RE: Making a tablespace read-only

From: Henry Poras <hporas_at_etal.uri.edu>
Date: Thu, 10 Oct 2002 09:09:18 -0800
Message-ID: <F001.004E5DAC.20021010090918@fatcity.com>

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" <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
---------------------------------------------------------------------
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: Henry Poras
  INET: hporas_at_etal.uri.edu

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 Oct 10 2002 - 12:09:18 CDT

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