Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Making a tablespace read-only - identifying blocking tx

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

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Thu, 10 Oct 2002 10:04:58 -0800
Message-ID: <F001.004E5F79.20021010100458@fatcity.com>


<Forehead Slap> 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?


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).
-- 
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 Thu Oct 10 2002 - 13:04:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US