Message-ID: <F001.004E5703.20021010030836@fatcity.com>
Date: Thu, 10 Oct 2002 03:08:36 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Rachel Carmichael <wisernet100@yahoo.com>
Subject: RE: Making a tablespace read-only - identifying blocking tx
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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@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@SLAC.Stanford.edu <mailto:ian@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@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@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).