Oracle FAQ Your Portal to the Oracle Knowledge Grid

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: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 09 Oct 2002 13:33:41 -0800
Message-ID: <>

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 <>

-----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:

Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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 Wed Oct 09 2002 - 16:33:41 CDT

Original text of this message