thanks :) it sort of makes sense that you can offline a tablespace with an active transaction in it. You can offline a rollback segment with an active transaction in it, no new transactions can start in that rbs but the one(s) there will finish.
With the tablespace, if I remember what I read in the docs correctly (and if the docs are correct), when you offline a tablespace, the active transactions in it continue to process as long as they do not try to modify a block in that tablespace. Once they try that, the transaction dies. Hm, that doesn't sound right...... maybe it's if the blocks from that tablespace are still in the buffer cache? Okay, found it in the 9ir2 Concepts manual, quoted below: When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tablespace. Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level. Oracle saves rollback data corresponding to those completed statements in a deferred rollback segment in the SYSTEM tablespace. When the tablespace is brought back online, Oracle applies the rollback data to the tablespace, if needed. When a tablespace goes offline or comes back online, this is recorded in the data dictionary in the SYSTEM tablespace. If a tablespace is offline when you shut down a database, the tablespace remains offline when the database is subsequently mounted and reopened. You can bring a tablespace online only in the database in which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database. An offline tablespace cannot be read or edited by any utility other than Oracle. Thus, offline tablespaces cannot be transposed to other databases. --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > First, I ommitted the ;) when replying to Rachel's post. I was just > funnin... > > Second, in this case TTS will not work. While we can 99.99% guarantee > that > there will be no tx against the tablespace, there will be active tx > in the > database and we cannot guarantee that they will not cause the RO > command to > wait. > > Interestingly, it is possible to offline the tablespace while there > are > active tx (even against objects in the ts). If you can offline and > then > online with active tx, why not alter it to RO? Perhaps, once again, I > am > missing the obvious. > > Dan > > -----Original Message----- > Sent: Thursday, October 10, 2002 8:23 PM > To: Multiple recipients of list ORACLE-L > > > yes it's relevant, it explains why you can't use a consistent export > only to move the data and have to copy the tablespace as well. It > also > explains why the tablespace has to be in read-only mode. > > > --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > > That information is not relevant and should be ignored. I would > > expect YOU > > to know as much! > > > > -----Original Message----- > > Sent: Thursday, October 10, 2002 1:25 PM > > To: Multiple recipients of list ORACLE-L > > > > > > ah! transportable tablespaces? you did't SAY that > > > > > > --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > > > It is not the export per se that causes the problem. It is the > > > copying of > > > the datafile that is the issue. The tablespace must be made read > > only > > > so > > > that the datafile can be copied in a consistent version. I can > > > understand > > > (and support) no active tx in the tablespace, but why the whole > > > (*#(&*$# > > > database? If I need to take INVOICE_1999 tablespace and migrate > it > > to > > > an > > > ODS, why does it matter if Joe Accountant is adding an expense > > report > > > in the > > > EXPENSE_2002 ts? > > > > > > In the Oracle doc, it lists the requirements for making a ts read > > > only. On > > > the next page it states (verbatim from doc) > > > > > > "You do not have to wait for transactions to complete before > > issuing > > > the > > > ALTER > > > TABLESPACE ... READ ONLY statement. When the statement is issued, > > the > > > target > > > tablespace goes into a transitional read-only mode in which no > > > further write > > > operations (DML statements) are allowed against the tablespace. > > > Existing > > > transactions that modified the tablespace are allowed to commit > or > > > rollback. > > > Once > > > all transactions (in the database) have completed, the tablespace > > > becomes > > > read-only." > > > > > > I love how Oracle buries a very important consideration in the > very > > > last > > > line of a paragraph! > > > > > > We are on 9ir1, so the TABLESPACE parameter is not helpful, but > we > > do > > > have > > > other options. The application architecture is such that I am > > pretty > > > certain > > > very bad things would happen if I tried to but the database in > > > restricted > > > mode. > > > > > > Dan > > > > > > -----Original Message----- > > > Sent: Thursday, October 10, 2002 5:09 AM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > 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" <[EMAIL PROTECTED]> 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 > > > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > > > > > > -----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). > > > > > === message truncated === __________________________________________________ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
