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

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

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