From dlandrum@zalecorp.com Wed, 18 Jun 2003 17:42:25 -0700 From: "Darrell Landrum" Date: Wed, 18 Jun 2003 17:42:25 -0700 Subject: Re: Partitions of table read only Message-ID: MIME-Version: 1.0 Content-Type: text/plain I don't know, the system in which I -alter tablespace sales_data read only -test some queries -drop table readtest -alter tablespace read write had 12+ sessions, and at least 3 were writing data, including one of my own. I'm not saying that what is being presented isn't true, just that you still have the opportunity for this to work. >>> [EMAIL PROTECTED] 06/18/03 06:25PM >>> ARGH! Well, that pretty much kills the idea of using this for my data warehouse as there is always activity in it. Dang! Okay, I'll try it from my laptop as I can control users there :) --- Daniel Fink <[EMAIL PROTECTED]> wrote: > 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] > > > > > > Visit our web site at 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 > === message truncated ===> 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 > __________________________________ 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: 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).