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/>
> > > >
> > > >
> > > > --
> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > > --
> > > > Author: Darrell Landrum
> > > > 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).
> > >
> > >
> > > __________________________________
> > > 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).
> >
> >
> > __________________________________
> > 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).
> >
> --
>
=== 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 Wed Jun 18 2003 - 14:28:24 CDT
