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...
- tx1 starts at time t1
- You alter tablespace tbsp1 to read only at t2 (it hangs)
- tx2 starts at time t3 and does not go against tbsp1
- tx3 starts at time t4 and does go against tbsp1 (it fails since tbsp1 is
in transitional read only)
- tx1 completes
- 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
> against
> > it. You
> > must wait till they all finish or kill them.
> >
> > A word of advice - if you decide to kill the sessions, bring the
> > tablespace
> > offline and then online to flush the buffers to disk. This will
> > ensure that
> > the delayed block cleanout will not occur and the contents will
> never
> > be
> > searched in an undo segment. This is not absolutely necessary, nor
> is
> > docuemnted anywhere, but in an active system I have seen ORA-1555
> > problems
> > surfacing.
> >
> > HTH.
> >
> > Arup
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> > Sent: Wednesday, June 18, 2003 1:29 PM
> >
> >
> > > okay, am I missing something?
> > >
> > > I created an LMT. Created a table in it. Gave no one quota on the
> > > tablespace.
> > >
> > > did (both as system and sysdba)
> > >
> > > alter tablespace test_drop read only;
> > >
> > >
> > > and hung
> > >
> > >
> > > what did I forget to do?
> > >
> > >
> > >
> > > --- Rachel Carmichael <[EMAIL PROTECTED]> wrote:
> > > > that actually makes sense when you think about it, with one
> > question
> > > > --
> > > > was the tablespace a dictionary-managed one or an LMT?
> > > >
> > > > If it was dictionary-managed, it makes perfect sense. The
> > metadata
> > > > about the table and the extents used in the tablespace are not
> > stored
> > > > IN that tablespace, so drop table would affect only the system
> > > > tablespace and the data dictionary.
> > > >
> > > > But in an LMT, the bitmap of extent usage is stored within the
> > > > tablespace itself, so I wonder if you could actually drop a
> table
> > > > from
> > > > a read-only lmt.
> > > >
> > > > Off to experiment......
> > > >
> > > > Rachel
> > > >
> > > > --- Darrell Landrum <[EMAIL PROTECTED]> wrote:
> > > > > I haven't tested this but would imagine it entirely possible.
> > > > > What I wanted to throw out though, is somewhat of a related
> > > > caution.
> > > > > You can drop a table from a read only tablespace. I
> discovered
> > > > this
> > > > > in
> > > > > test, fortunately when I was finished testing with that table
> > and
> > > > > intentionally dropped it while the tablespace was in read
> only
> > > > mode.
> > > > >
> > > > > >>> [EMAIL PROTECTED] 06/18/03 09:49AM >>>
> > > > > Hi,
> > > > >
> > > > > I would like to know if it is possible and what the pitfalls
> > are if
> > > >
> > > > > I
> > > > > do
> > > > > the following.
> > > > >
> > > > > Partition a large table into partitions based on date.
> > > > > Data is only entered and read and never altered, so I would
> > like to
> > > > > move
> > > > > older partitions to read only tablespaces and possible read
> > only
> > > > > devices so
> > > > > the backup will be made quicker.
> > > > >
> > > > > Is it possible to have parttions of the same table spread
> > across
> > > > read
> > > > > only
> > > > > and read/write tablespaces?
> > > > > Am I correct in assuming that once you backup a read only
> > > > tablespace
> > > > > there
> > > > > is no need to backup the same again. (provided you don't make
> > it
> > > > > read/write
> > > > > add data and make it read only again).?
> > > > > Does anybody have a procedure already that automatically
> > creates
> > > > the
> > > > > new
> > > > > partitions let say every month?
> > > > >
> > > > >
> > > > > TIA
> > > > >
> > > > > Jacob A. van Zanen
> > > > > Oracle DBA
> > > > > Quant Systems Europe b.v.
> > > > > Tel : +31 (0) 251 - 268 268
> > > > > Mobile: +31 (0) 6 51308813
> > > > > Fax: +31 (0) 251 - 268 269
> > > > > E-mail: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> > > > > Visit our web site at http://www.quantsystems.nl/
> > > > > <http://www.quantsystems.nl/>
> > > > >
> > > > >
> > > > > --
>
=== 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johnston, Tim
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:24:33 CDT
