Re: Partitions of table read only

Rachel,

        It is not active transactions against that tablespace, it is active transactions. Yup, period! As soon as all the active transactions complete, the tablespace will complete altering itself.

Dan

Rachel Carmichael wrote:
>
> 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).begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard Received on Wed Jun 18 2003 - 16:21:33 CDT