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

[Deshpande, Kirti]

Title: Message

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-----
From: MacGregor, Ian A. [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 09, 2002 4:34 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Making a tablespace read-only - identifying blocking tx

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]

-----Original Message-----
From: Fink, Dan [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 09, 2002 1:49 PM
To: Multiple recipients of list ORACLE-L
Subject: Making a tablespace read-only - identifying blocking tx

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