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).
Received on Thu Jun 19 2003 - 12:40:36 CDT
