much better -- now I understand :)
--- "Johnston, Tim" <[EMAIL PROTECTED]> wrote: > Ah... That's why the example helps... The text above the example is > unclear > but the example is a little bit better... > > > > > "If you find it is taking a long time for the tablespace to quiesce, > it is > possible to identify the transactions which are preventing the > read-only > state from taking effect. The owners of these transactions can be > notified > and a decision can be made to terminate the transactions, if > necessary. The > following example illustrates how you might identify the blocking > transactions. > > Identify the transaction entry for the ALTER TABLESPACE...READ ONLY > statement. > > SELECT sql_text, saddr > FROM v$sqlarea,v$session > WHERE v$sqlarea.address = v$session.sql_address > AND sql_text like 'alter tablespace%'; > > SQL_TEXT SADDR > ---------------------------------------- -------- > alter tablespace tbs1 read only 80034AF0 > > > The start SCN of each active transaction is stored in the > V$TRANSACTION > view. Displaying this view sorted by ascending start SCN lists the > transactions in execution order. Knowing the transaction entry for > the > read-only statement, it can be located in the V$TRANSACTION view. All > transactions with lesser or equal start SCN can potentially hold up > the > quiesce and subsequent read-only state of the tablespace. > > SELECT ses_addr, start_scnb > FROM v$transaction > ORDER BY start_scnb; > > SES_ADDR START_SCNB > -------- ---------- > 800352A0 3621 --> waiting on this txn > 80035A50 3623 --> waiting on this txn > 80034AF0 3628 --> this is the ALTER TABLESPACE statement > 80037910 3629 --> don't care about this txn > > > After making the tablespace read-only, it is advisable to back it up > immediately. As long as the tablespace remains read-only, no further > backups > of the tablespace are necessary since no changes can be made to it." > > > > > Notice it says "All transactions with lesser or equal start SCN can > potentially hold up the quiesce and subsequent read-only state of the > tablespace"... Not just the transactions against that tablespace... > And, > they are checking all entries in v$transaction... > > > On the other hand, you can still alter tablespaces to read only even > though > your system is busy... The point is that the command will hang until > all > transactions that started before your alter command finish... Maybe > this is > a better way... > > 1) tx1 starts at time t1 > 2) You alter tablespace tbsp1 to read only at t2 (it hangs) > 3) tx2 starts at time t3 and does not go against tbsp1 > 4) tx3 starts at time t4 and does go against tbsp1 (it fails since > tbsp1 is > in transitional read only) > 5) tx1 completes > 6) The alter to read only can not complete since tx1 was the only > transaction that started before it > > Better? > > Tim > > > -----Original Message----- > Sent: Wednesday, June 18, 2003 9:50 PM > To: Multiple recipients of list ORACLE-L > > > The admin guide doesn't say "no transactions in the database". In > fact, > it specifically says in the tablespace: > > " 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 DML statements are allowed, though existing > transactions that modified the tablespace will be allowed to commit > or > rollback. Once this occurs, the tablespace is quiesced, with respect > to > active transactions." > > There were other transactions in the database, but none affecting > that > tablespace. > > The concepts guide is somewhat ambiguous and could be read either as > "in the database" or "against the tablespace" since it isn't > specified. > > > Have I mentioned lately that I hate the docs? :) > > > > > --- "Johnston, Tim" <[EMAIL PROTECTED]> wrote: > > In order to complete an alter to read only, ALL transactions > against > > the > > database that were started before you issued that alter command > must > > complete before the alter will continue... From the concepts > > guide... > > > > > > The ALTER TABLESPACE ... READ ONLY statement places the tablespace > in > > a > > transitional read-only mode and waits for existing transactions to > > complete > > (commit or roll back). This transitional state does not allow any > > further > > write operations to the tablespace except for the rollback of > > existing > > transactions that previously modified blocks in the tablespace. > > Hence, in > > transition the tablespace behaves like a read-only tablespace for > all > > user > > statements except ROLLBACK. After all of the existing transactions > > have > > either committed or rolled back, the ALTER TABLESPACE ... READ ONLY > > statement completes and the tablespace is placed in read-only mode. > > > > > > > And there is a good example in the admin guide... > > > > > http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspa > > ces.htm#6884 > > > > HTH > > > > Tim > > > > -----Original Message----- > > Sent: Wednesday, June 18, 2003 3:30 PM > > To: Multiple recipients of list ORACLE-L > > > > > > there WERE no active transactions against that tablespace. > > > > The steps I took were: > > > > as system: > > 1) create tablespace as an LMT > > 2) create table within that tablespace > > 3) attempt to make the tablespace read-only > > when that hung I logged out (which certainly killed any active > > transactions against that tablespace!) > > > > 4) log back in as / as sysdba > > 5) attempt to make that tablespace read-only > > > > No one else knows about that tablespace, it's brand-new. No one > else > > has quota or access on the table I created. > > > > > > However, for completeness, I just offlined and onlined that > > tablespace, > > then tried to make it read only. It's still hanging. > > > > Oh yeah, 9.2.0.1 on Linux > > > > Rachel > > > > --- Arup Nanda <[EMAIL PROTECTED]> wrote: > > > Rachel, > > > > > > A TS can't become read only if there are active transactions > === message truncated === __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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).
