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

From: <Chaim.Katz_at_Completions.Bombardier.com>
Date: Wed, 09 Oct 2002 13:43:44 -0800
Message-ID: <F001.004E4F95.20021009134344@fatcity.com>

Here's an even more kludgy guess. Maybe you can execute the alter tablespace in the pl/sql job queue. If it runs too long (how long is too long?), then you know that it's waiting (i.e., failing), and you could do something appropriate action?

"Fink, Dan" <Dan.Fink_at_mdx.com>@fatcity.com on 10/09/2002 04:48:54 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

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:
  INET: Chaim.Katz_at_Completions.Bombardier.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 Wed Oct 09 2002 - 16:43:44 CDT