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] <mailto:[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
> <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: [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, 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).

Reply via email to